<?php
/**
 * @author: peter
 * @email: peter.setier@dwebsite.com
 * @on: July 17, 2008
 *
 */
 

 Zend_Loader::loadClass("CodesUtility");
 Zend_Loader::loadClass("TimeUtility");
 Zend_Loader::loadClass("AccountingInvoice");
 Zend_Loader::loadClass("AccountingJournal");
 Zend_Loader::loadClass("AccountingJournalRegistration");
 Zend_Loader::loadClass("AccountingJournalItem");
 Zend_Loader::loadClass("AccountCodes");
 Zend_Loader::loadClass("Registration");
 Zend_Loader::loadClass("RegistrationChild");
 Zend_Loader::loadClass("RegistrationCosponsor");
 Zend_Loader::loadClass("BaseLogger");
 
 class PaymentUtility
 {
	
	/**
	 * description: generates an invoice for nsf check 
	 *		charge. it adds the neccessary entries in 
	 *		the database. 
	 *
	 *		returns the id of the new row in the 
	 *		accounting_nsf_check table.
	 * @author: Stacey K.D. Cardoso
	 * @email: stacey.cardoso@dwebsite.net
	 **/
	function generateNsfInvoice(
		$check_payment_id = 0,
		$nsfChargeAmount = 0,
		$duedate = '')
	{
		if(!$check_payment_id)
			return 0;
		
		Zend_Loader::loadClass("AccountCode");
		Zend_Loader::loadClass("AccountCodes");
		Zend_Loader::loadClass("AccountingInvoice");
		Zend_Loader::loadClass("AccountingJournal");
		Zend_Loader::loadClass("AccountingJournalItem");
		Zend_Loader::loadClass("AccountingJournalItemCharge");
		Zend_Loader::loadClass("AccountingJournalItemCharges");
		Zend_Loader::loadClass("AccountingJournalRegistration");
		Zend_Loader::loadClass("AccountingNsfCheck");
		Zend_Loader::loadClass("AccountingPayment");
		Zend_Loader::loadClass("CodesUtility");
		Zend_Loader::loadClass("MiscellaneousCharges");
		Zend_Loader::loadClass("SetupUtility");
		Zend_Loader::loadClass("TimeUtility");
		
		
		$accountCodes = new AccountCodes();
		$accountCodesArray = $accountCodes->getAccountCodesArray(
			"short_name", 
			"account_code");
				
		$nsfCharge = new MiscellaneousCharge();
		$nsfCharge->clear();
		$nsfCharge->loadWhere(
			" name = '".CodesUtility::NSF_CHECK_CHARGE_NAME."'");
		
		if(!$nsfCharge < 1)
		{
			/*Array ( 
			[id] => 0 
			[name] => Stacey's Misc Charge 
			[amount] => 4 
			[account_code_id] => 7 
			[description] => 
			[fee_type_id] => 1 ) 
			 */
			/*
			Array ( 
				[id] => 0 
				[name] => NSF Check Charge 
				[amount] => 5 
				[account_code_id] => 11 
				[description] => 
				[fee_type_id] => 1 ) 
			*/
			$fee_type = new FeeType();
			$fee_type->load(SetupUtility::FEE_FLAT);
		
			$code = new AccountCode();
			$code->clear();
			$code->loadWhere(
				"account_code = ".
				"'".$accountCodesArray[CodesUtility::ACCOUNT_CODE_NSF]."'");
			$valArray = array(
				"name"=>CodesUtility::NSF_CHECK_CHARGE_NAME,
				"amount"=>$nsfChargeAmount,
				"account_code_id"=>$code->id,
				"description"=>"",
				"fee_type_id"=>$fee_type->id);
				
			// add nsf charge to charges table
			$nsfCharge->setFromArray($valArray);
			
			$nsfCharge->save();
			if($nsfCharge->id < 1)
			{
				//echo "could not create nsf charge"; die();
				return 0;
			}
		}
		
		$nsfChargeId = $nsfCharge->id;
		
		if(!$nsfChargeId || 
			  $nsfChargeId < 1)
		{
			//echo "nsf charge id is less than 1"; die();
			return 0;
		}
		
		$connection = DbCon::instance()->getConnection();
		try
		{
			$connection->beginTransaction();
	
			$checkPayment = new AccountingPayment();
			$checkPayment->loadById($check_payment_id);
			
			if(!$checkPayment)
			{
				//echo "nsf check payment does not exist"; die();
				return 0;
			}
			
			$checkJournal = new AccountingJournal();
			$checkJournal->loadById($checkPayment->journal_id);
			
			if(!$checkJournal)
			{
				//echo "check journal does not exist"; die();
				return 0;
			}
			
			$family_account_id = $checkJournal->family_account_id;
			$payer_id = $checkPayment->payer_id;
			
			
			//Step 1: create journal
			$journal = new AccountingJournal();
			$journal->clear();
			$journal->transaction_type = 
				CodesUtility::TRANS_TYPE_INVOICE;
			$journal->transaction_date = 
				TimeUtility::convertToSQLDateFormat(
					date('m/d/Y H:i:s') , 
					true);
			$journal->family_account_id =  $family_account_id;
			$journal->posted = 1;
			$journal->save();
			//print_r("journal id: ".$journal->id);
			if(!$journal || 
				$journal->id < 1)
			{
				//echo "unable to generate journal"; die();
				$connection->rollBack();
				return 0;
			}
			
			//Step 2: get the due date for the invoice
			Zend_Loader::loadClass("PricingSetting");
			// todo add due date, check the setup 
			$settings = new PricingSetting();
			$settings->clear();
			$settings->loadWhere(
				"code = '".
				CodesUtility::PRICING_PERIOD_DUE_DATE. 
				"'");
			$day = $settings->value;

			$settings->clear();
			$settings->loadWhere(
				"code = '". 
				CodesUtility::PRICING_PERIOD_DUE_DATE_ORDER.
				"'");
			$order = $settings->value;
			$today = getdate();
			if($order)
			{
				// after - next month
				$month = $today['mon'] + 1;
				
				if($month > 12)
				{
					$year = $today['year'] + 1;
					$month = 1;
				} 
				else 
				{
					$year = $today['year'];
				}
				
				$duedate = $year.'-'.$month.'-'.$day;
			} 
			else 
			{
				// before - same month
				if($day >= $today['mday'] ) 
				{
					$duedate =  
						$today['year'].'-'.
						$today['mon'].'-'.
						$today;
				} 
				else 
				{
					$duedate =  
						$today['year'].'-'.
						$today['mon'].'-'.
						$today['mday'];
				}
			}
			
			// Step 3: create the invoice
			$invoice = new AccountingInvoice();
			$invoice->clear();
			$invoice->journal_id = $journal->id;
			//$invoice->notes = "NSF Fee for 
			$invoice->date_created = 
				TimeUtility::convertToSQLDateFormat(
					date('m/d/Y H:i:s'), 
					true);
			$invoice->date_due = $duedate;
			$invoice->save();
			// update invoice no
			$invoice->invoice_number = 
				CodesUtility::STARTING_INVOICE_NO + $invoice->id;
			$invoice->payer_id=$payer_id;
			$invoice->save();
			//print_r("invoice id: ".$invoice->id);
			if(!$invoice || 
				$invoice->id < 1)
			{
				//echo "unable to generate invoice"; die();
				$connection->rollBack();
				return 0;
			}
			
			$regId = 0;
			$journalRegItem = new AccountingJournalRegistration();
			$journalRegItem->clear();
			$journalRegItem->loadWhere(
				"accounting_journal_id=".$checkPayment->journal_id);
			if(!$journalRegItem ||
				  $journalRegItem->registration_id < 1)
			{
				$journalChargeItem = new AccountingJournalItemCharge();
				$journalChargeItem->clear();
				$journalChargeItem->loadWhere(
					"accounting_journal_id=".$checkPayment->journal_id);
				
				if(!$journalChargeItem || 
					  $journalChargeItem->registration_id < 1)
				{
					//echo "unable to generate journal charge item"; die();
					$connection->rollBack();
					return 0;
				}
				
				$regId = $journalChargeItem->registration_id;
			}
			else
			{
				$regId = $journalRegItem->registration_id;
			}
			
			if($regId < 1)
			{
				//echo "unable to retrieve reg id"; die();
				$connection->rollBack();
				return 0;
			}
			
			//Step 4: create the accounting journal item charge
			$journalItemCharge = new AccountingJournalItemCharge();
			$journalItemCharge->clear();
			$journalItemCharge->accounting_journal_id = $journal->id;
			$journalItemCharge->charge_id = $nsfChargeId;
			$journalItemCharge->registration_id = $regId;
			$journalItemCharge->accounting_invoice_id = $invoice->id;
			// $journalItemCharge->child_id = $childId;
			if($nsfChargeAmount && 
				$nsfChargeAmount > 0)
			{
				$journalItemCharge->amount = $nsfChargeAmount;
			}
			else
				$journalItemCharge->amount = $nsfCharge->amount;
				
			$journalItemCharge->fully_refunded = 0;
			$journalItemCharge->save();
			if(!$journalItemCharge ||
				$journalItemCharge->id < 1)
			{
				//echo "unable to generate journal item charge"; die();
				$connection->rollBack();
				return 0;
			}
			
			
			//Step 5: create the journal items (debit and credit)
			$journalitems = new AccountingJournalItem();
			$journalitems->clear();
			$journalitems->journal_id = $journal->id;
			$journalitems->account_code = 
				$accountCodesArray[CodesUtility::ACCOUNT_CODE_AR];
			if(!$nsfChargeAmount ||
				$nsfChargeAmount <= 0)
			{
				$journalitems->cost = $nsfCharge->cost;
			}
			else
			{
				$journalitems->cost = $nsfChargeAmount;
			}
			$journalitems->accounting_type = 
				CodesUtility::ACCOUNTING_TYPE_DEBIT;
			$journalitems->account_type = 
				CodesUtility::ACCOUNT_CODE_AR;
			$journalitems->save();
			if(!$journalitems || 
				$journalitems->id < 1)
			{
				//echo "unable to generate journal item."; die();
				$connection->rollBack();
				return 0;
			}
			
			//print_r("journal item debit: ".$journalitems->id);
			$journalitems->clear();
			$journalitems->journal_id = $journal->id;
			$journalitems->account_code = 
				$accountCodesArray[CodesUtility::ACCOUNT_CODE_NSF];
			if(!$nsfChargeAmount || 
				$nsfChargeAmount <= 0)
			{
				$journalitems->cost = $nsfCharge->cost;
			}
			else
			{
				$journalitems->cost = $nsfChargeAmount;
			}
			
			$journalitems->accounting_type = 
				CodesUtility::ACCOUNTING_TYPE_CREDIT;
			$journalitems->account_type = 
				CodesUtility::ACCOUNT_TYPE_MISC_CHARGES;
			$journalitems->save();
			//print_r("journal item debit: ".$journalitems->id);	
			if(!$journalitems || 
				$journalitems->id < 1)
			{
				//echo "unable to generate journal item."; die();
				$connection->rollBack();
				return 0;
			}
			
			//Step 6: create the nsf check
			$nsfCheck = new AccountingNsfCheck();
			$nsfCheck->clear();
			$nsfCheck->accounting_payment_id = 
				$check_payment_id;
			$nsfCheck->accounting_journal_id = 
				$journal->id;
			$nsfCheck->last_updated_on = date("Y-m-d G:i:s");
			$nsfCheck->last_edited_by_id = 
				AuthUtility::getCurrentUserId();
			$nsfCheck->save();
			
			if(!$nsfCheck || 
				$nsfCheck->id < 1)
			{
				//echo "unable to generate nsf check."; die();
				$connection->rollBack();
				return 0;
			}
			else
			{
				$connection->commit();
				return $nsfCheck->id;
			}
		}
		catch(Exception $e)
		{
			//echo $e->getMessage(); die();
			$connection->rollBack();
			return 0;
		}
	}
		
	
	function generateInvoice($registration_id,$notes = "",$payer_id=0,$misccost=0,$misc=FALSE,$miscCharge=null,$family_account_id=0,$cosponsor=0,$duedate='',$param=array(),$regcostarray=array(),$shownotesin = array()){
	
		/**
		 * Modified so that the notes are
		 *	alse recorded in the journals
		 *	and its display options are saved
		 *	in accounting_journals_notes_view
		 *
		 * @modified: Stacey K.D. Cardoso
		 * @email: stacey.cardoso@dwebsite.net
		 * @on: 1-April-2009
		 **/
		Zend_loader::loadClass("RegistrationMonth");
		Zend_Loader::loadClass("CodesUtility");
		Zend_Loader::loadClass("AccountingJournalNotesView");
		
	
		if($family_account_id){
			
				// journal registration reference
				
				$registration = new Registration();
				$registrationmonth = new RegistrationMonth();
				$registration_co_sponsor = new RegistrationCosponsor();
				$initial_registrationmonth = new RegistrationMonth();
				
				
				if($family_account_id == 0){
					#get the main payer for this familyaccount
					if(!empty($registration_id) > 0){
						$registration->loadByid($registration_id[0]['registration_id']);
						$family_account_id =  $registration->family_account_id;
					}
				}
				
				if($payer_id==0){
					Zend_Loader::loadClass("Payer");
					$mainpayer = new Payer();
					$mainpayer->loadMainPayer($family_account_id);
					//$mainpayerValue = $mainpayer->toArray();
					$payer_id = $mainpayer->id;
				}
				
				$journal = new AccountingJournal();	
				$invoice = new AccountingInvoice();
				$journalitems = new AccountingJournalItem();
				$journalNotesView = new AccountingJournalNotesView();
				
				// create journal
				$journal->clear();
				$journal->transaction_type = CodesUtility::TRANS_TYPE_INVOICE;
				$journal->transaction_date = TimeUtility::convertToSQLDateFormat(date('m/d/Y H:i:s') , true);
				$journal->family_account_id =  $family_account_id;
				$journal->posted = 1;
				$journal->notes = $notes;
				$journal->save();
				
				if(strlen($notes) > 0 && !empty($shownotesin))
				{
					$journalNotesView->clear();
					$journalNotesView->accounting_journal_id = $journal->id;
					$journalNotesView->show_notes_on = "";
					
					foreach($shownotesin as $show)
					{
						if($show != CodesUtility::PRINTED_RECEIPT &&
							$show != CodesUtility::EMAIL_RECEIPT &&
							$show != CodesUtility::PUBLIC_INVOICE)
							continue;
						
						if(strlen($journalNotesView->show_notes_on) > 0)
							$journalNotesView->show_notes_on .= ",";
							
						$journalNotesView->show_notes_on .= $show;
					}
					
					if(strlen($journalNotesView->show_notes_on) > 0)
						$journalNotesView->save();
				}
		
				if(count($registration_id) > 0) {
					$accountingjournal_item = new AccountingJournalRegistration();
					$cost = 0;
					foreach($registration_id as $key => $value){

						
						// create invoice
						if(!$invoice->id){
						
							if($duedate == ''){
								Zend_Loader::loadClass("PricingSetting");
			
								$day = 0;
								$settings = new PricingSetting();
								$settings->clear();
								$settings->loadWhere("code = '".CodesUtility::PRICING_PERIOD_DUE_DATE. "'");
								$day = $settings->value;
			
								$settings->clear();
								$settings->loadWhere("code = '". CodesUtility::PRICING_PERIOD_DUE_DATE_ORDER."'");
								$order = $settings->value;
								$today = getdate();
								if($order){
									// after - next month
									$month = $value['month'] + 1;
									if($month > 12){
										$year = $value['year'] + 1;
										$month = 1;
									} else {
										$year = $value['year'];
									}
									$duedate = $year.'-'.$month.'-'.$day;
								} else {
									// before - same month
									if($day >= $today['mday'] ) {
										$duedate =  $value['year'].'-'.$value['month'].'-'.$day;
									} else {
										$duedate =  $value['year'].'-'.$value['month'].'-'.$today['mday'];
									}
								}	
							}
						
							$invoice->clear();
							$invoice->journal_id = $journal->id;
							$invoice->notes = $notes;
							$invoice->date_created = TimeUtility::convertToSQLDateFormat(date('m/d/Y H:i:s'), true);
							$invoice->date_due = $duedate;
							$invoice->save();
							// update invoice no
							$invoice->invoice_number = CodesUtility::STARTING_INVOICE_NO + $invoice->id;
							$invoice->payer_id=$payer_id;
							$invoice->save();
						}
					
						$reg_id = $value['registration_id'];
						
						$regcost = 0; 	
						$pricetiercost = 0;
						$registration_month_id = 0;
						if(isset($value['registration_month_id'])) {
							$registration_month_id = $value['registration_month_id'];
						} else {
							$registrationmonth->clear;
							$registrationmonth->loadWhere("registration_id = ".$reg_id ." and month = ". $value['month']. " and year = ".$value['year']);
							$regvalues = $registrationmonth->toArray();
							$registration_month_id = $regvalues['id'];
						}
						
						if($registration_month_id) {
							#check if the  invoice is a co-sponsor then use the amount in the table registration co-sponsor
							if($cosponsor){
								$registration_co_sponsor->clear;
								$regcosponsorValues = $registration_co_sponsor->cosponsorAmountByReg($reg_id,$cosponsor,false);
								
								$cost = $cost + $regcosponsorValues[0]['sponsor_amount'];
								$regcost = $regcosponsorValues[0]['sponsor_amount'];
								$registration_co_sponsor->clear;
								$registration_co_sponsor->assignInvoice($reg_id,$invoice->id,$cosponsor);
							} else {
								$registration_co_sponsor->clear;
								$regcosponsorValues = $registration_co_sponsor->cosponsorAmountByReg($reg_id);
								if(isset($value['amount']) || isset($value['due'])) {
								
									if(isset($value['due'])) {
										$reg_cost = $value['due'];
									} else {
										if(isset($value['amount']) && $reg_cost == 0)
											$reg_cost = $value['amount'];
										if(isset($value['discount'])  && $value['discount'] > 0 )
											$reg_cost = $reg_cost - $value['discount'];
										if(isset($value['scholarship']) && $value['scholarship'] > 0 ) 
											$reg_cost = $reg_cost - $value['scholarship'];
										if(isset($value['pricetier'])	&& $value['pricetier'] > 0 ) 
											$reg_cost = $reg_cost - $value['pricetier'];
									}
									
									$sponsor = 0 ;
								} else {
									$reg_cost = $registrationmonth->cost - $registrationmonth->discount - $registrationmonth->scholarship;
									$sponsor = $regcosponsorValues[0]['sponsor_amount'];
								}
								
								$cost = $cost + $reg_cost - $sponsor;
								$regcost = $reg_cost - $sponsor;
								
							}
							
								$accountingjournal_item->clear();
								$accountingjournal_item->accounting_journal_id = $journal->id;
								$accountingjournal_item->registration_id = $reg_id;
								$accountingjournal_item->registration_month_id = $registration_month_id;
								$accountingjournal_item->amount = $regcost;
								$accountingjournal_item->accounting_invoice_id = $invoice->id;
								$accountingjournal_item->save();	
						}
						
						
					}
				}
				
				
				#miscellaneous details
				if(count($miscCharge) > 0) {
					Zend_loader::loadClass('AccountingJournalItemCharge');
					
					$accountingjournal_item = new AccountingJournalRegistration();
					$newmisc = new AccountingJournalItemCharge();	
					
					foreach ($miscCharge as $key => $value){
					
						$splitvalue = array();
						$splitvalue = explode(',',$value);
						
						$child = $splitvalue[0];
						$amount = $splitvalue[1];
						$registration_id = $splitvalue[2];
						$newmisc->accounting_journal_id = $journal->id;
						$newmisc->charge_id = $key;
						$newmisc->registration_id = $registration_id;
						$newmisc->accounting_invoice_id = $invoice->id;
						
						if($child){
							$newmisc->child_id = $child;
						} else {
							$newmisc->child_id = 0;
						}
						$newmisc->amount =  $amount;
						$newmisc->save();
						$newmisc->clear();
						
					}
				}
				
				
				
				
				// general journal details
				// locate the account code of each account
					$accountCodes = new AccountCodes();
					$accountCodesArray = $accountCodes->getAccountCodesArray("short_name", "account_code");
				if($misc==FALSE){
					$journalitems->clear();
					$journalitems->journal_id = $journal->id;
					$journalitems->account_code = $accountCodesArray[CodesUtility::ACCOUNT_CODE_AR];
					$journalitems->cost = $cost;
					$journalitems->accounting_type = CodesUtility::ACCOUNTING_TYPE_DEBIT;
					$journalitems->account_type = CodesUtility::ACCOUNT_TYPE_REGISTRATIONS;
					$journalitems->save();
				
					$journalitems->clear();
					$journalitems->journal_id = $journal->id;
					$journalitems->account_code = $accountCodesArray[CodesUtility::ACCOUNT_CODE_SALES];
					$journalitems->cost = $cost;
					$journalitems->accounting_type = CodesUtility::ACCOUNTING_TYPE_CREDIT;
					$journalitems->account_type = CodesUtility::ACCOUNT_TYPE_REGISTRATIONS;
					$journalitems->save();
				} else {
					$journalitems->clear();
					$journalitems->journal_id = $journal->id;
					$journalitems->account_code = $accountCodesArray[CodesUtility::ACCOUNT_CODE_AR];
					$journalitems->cost = $misccost;
					$journalitems->accounting_type = CodesUtility::ACCOUNTING_TYPE_DEBIT;
					$journalitems->account_type = CodesUtility::ACCOUNT_TYPE_MISC_CHARGES;
					$journalitems->save();
				
					$journalitems->clear();
					$journalitems->journal_id = $journal->id;
					$journalitems->account_code = $accountCodesArray[CodesUtility::ACCOUNT_CODE_MISC];
					$journalitems->cost = $misccost;
					$journalitems->accounting_type = CodesUtility::ACCOUNTING_TYPE_CREDIT;
					$journalitems->account_type = CodesUtility::ACCOUNT_TYPE_MISC_CHARGES;
					$journalitems->save();
				}
				
					// ends here
				return $invoice->id;
		} return 0;
				
	}
	
	public function getRefundByInvoice($invoice_id){
		Zend_Loader::loadClass("AccountingRefund");
		Zend_Loader::loadClass("AccountingJournalRegistration");
		
		$query = new QueryCreator();
		$query->addSelect(" sum(amount) as refund_amount");
		$query->addFrom( AccountingRefund::_tablename . " a ");
		$query->addJoin(" LEFT JOIN ". AccountingJournalRegistration::_tablename . " b on a.journal_id = b.accounting_journal_id");
		$query->addWhere(" b.accounting_invoice_id = ". $invoice_id );
		$db = DBCon::instance();
		$result = $db->executeQuery($query->createSQL());
		
		return $result;
		
	}
	
	function getInvoice($invoice_id){
		//print_r("invoice id: ".$invoice_id."<br><br>");
		Zend_Loader::loadClass("ChildInfo");
		Zend_Loader::loadClass("BaseCatalog");
		Zend_Loader::loadClass("AccountingPayment");
		Zend_Loader::loadClass("AccountingPaymentInvoice");
		Zend_Loader::loadClass("Payer");
		Zend_Loader::loadClass("PayerType");
		Zend_Loader::loadClass("RegistrationCosponsor");
		Zend_Loader::loadClass("RegistrationMonth");
		Zend_Loader::loadClass("AccountingJournalItemCharge");
		Zend_Loader::loadClass("AccountingRefund");
		Zend_Loader::loadClass("RegistrationAdditionalCost");
		Zend_Loader::loadClass("MiscellaneousCharge");
		Zend_Loader::loadClass("AccountingNsfCheck");
		Zend_Loader::loadClass("AccountingPaymentCash");
		
		$db = DBCon::instance();
		$query = new QueryCreator();
		$query->addSelect(" IF(rac.id>0,concat(name,' - (For Added Day ',date_format(rac.scheduled_date,'%m/%d/%Y'),' )'),concat(name,' - ',if(m.month = 0,'Reg. Fee',concat(monthname(concat(m.year,'-',m.month,'-01')),' ',m.year) ) ) ) as session_name,id_number as session_id,concat(e.firstname,' ',e.lastname) as child_name,sum(b.amount)  as cost");
		
		$query->addSelect(" ifnull(( select sum(amount) from ". AccountingPayment::_tablename ." ap left join ". AccountingPaymentInvoice::_tablename ." api on ap.id=api.accounting_payment_id  left join ". AccountingJournal::_tablename  ." aj1 on aj1.id=ap.journal_id  left join ". AccountingJournalRegistration::_tablename ." ajr1 on ajr1.accounting_journal_id = ap.journal_id  and ajr1.accounting_invoice_id =  api.accounting_invoice_id where b.registration_id=ajr1.registration_id and b.accounting_invoice_id=ajr1.accounting_invoice_id and b.registration_month_id=ajr1.registration_month_id and aj1.cancelled_date is null ),0)  as paid" );
		
		$query->addSelect(" 0 as balance");
		$query->addSelect(" date_format(date_created,'%m/%d/%Y') as invoice_date,a.notes,a.invoice_number,j.firstname,j.lastname,j.address,j.city,j.state,j.zip,k.value as payer_type , a.payer_id as payer_id,date_format(date_due,'%m/%d/%Y') as date_due");
		
		$query->addSelect(" l.amount as cosponsor_amount,show_parent_amount_due,show_no_of_care_hours,show_no_of_care_days ");
		$query->addSelect(" m.year as reg_year,m.month as reg_month,f.start_date as session_start_date,c.id as reg_id");
		
		// invoice is the base
		$query->addFrom( AccountingJournalRegistration::_tablename ." b ");
		$query->addJoin(" left join " . AccountingInvoice::_tablename ." a on a.journal_id=b.accounting_journal_id ");
		$query->addJoin(" left join " . Registration::_tablename . " c on c.id=b.registration_id ");
		$query->addJoin(" left join " . RegistrationChild::_tablename . " d on d.registration_id = c.id "); 
		$query->addJoin(" left join " . ChildInfo::_tablename . " e on e.id=d.child_id ");
		$query->addJoin(" left join " . BaseCatalog::_tablename . " f on f.id = c.catalog_session_id");
		$query->addJoin(" left join " . RegistrationMonth::_tablename . " as m	  on m.registration_id=c.id and b.registration_month_id=m.id");
	
		$query->addJoin(" left join " . Payer::_tablename ." j on j.id = a.payer_id ");
		$query->addJoin(" left join " . PayerType::_tablename ." k on k.id = j.payer_type_id");
		$query->addJoin(" left join " . RegistrationCosponsor::_tablename ." l on l.registration_id = c.id and a.id=l.accounting_invoice_id ");
		$query->addJoin(" LEFT JOIN ". RegistrationAdditionalCost::_tablename ." rac on rac.accounting_invoice_id = a.id ");
		$query->addWhere(" a.id =  ".$invoice_id);
		$query->addGroupby(" a.id,b.registration_id,b.id ");
		$query->addHaving(" session_name IS NOT NULL");
		
		
		
		$query2 = new QueryCreator();
		$query2->addSelect(" concat(f.name,'<br>','(',ch.name,')') as session_name,id_number as session_id,concat(e.firstname,' ',e.lastname) as child_name,b.amount   as cost,ifnull(sum(if(n.cancelled_date is null > 0,i.amount,0)),0) - ifnull(( select sum(amount) from ". AccountingRefund::_tablename . " ar left join ". AccountingJournalItemCharge::_tablename ." ajic on ar.journal_id=ajic.accounting_journal_id left join ". AccountingJournal::_tablename ." aj on aj.id=ar.journal_id where aj.cancelled_date is null and ajic.accounting_invoice_id=b.accounting_invoice_id and ajic.registration_id=b.registration_id and ajic.charge_id=b.charge_id and ajic.child_id=b.child_id ),0) as paid, b.amount - ifnull(sum(i.amount),0) + ifnull(( select sum(amount) from ". AccountingRefund::_tablename . " ar left join ". AccountingJournalItemCharge::_tablename ." ajic on ar.journal_id=ajic.accounting_journal_id left join ". AccountingJournal::_tablename ." aj on aj.id=ar.journal_id where aj.cancelled_date is null and ajic.accounting_invoice_id=b.accounting_invoice_id and ajic.registration_id=b.registration_id and ajic.charge_id=b.charge_id and ajic.child_id=b.child_id ),0) balance,date_format(date_created,'%m/%d/%Y') as invoice_date,a.notes,a.invoice_number,j.firstname,j.lastname,j.address,j.city,j.state,j.zip,k.value as payer_type , a.payer_id as payer_id,date_format(date_created,'%m/%d/%Y') as date_due");
		$query2->addSelect(" l.amount as cosponsor_amount,show_parent_amount_due,show_no_of_care_hours,show_no_of_care_days ");
		$query2->addSelect(" f.season_period_year as reg_year,0 as reg_month,f.start_date as session_start_date,c.id as reg_id");
		
		// charges
		$query2->addFrom( AccountingJournalItemCharge::_tablename ." b ");
		$query2->addJoin(" left join " . AccountingInvoice::_tablename ." a on a.journal_id=b.accounting_journal_id ");
		$query2->addJoin(" left join " . Registration::_tablename . " c on c.id=b.registration_id ");
		$query2->addJoin(" left join " . RegistrationChild::_tablename . " d on d.registration_id = c.id "); 
		$query2->addJoin(" left join " . ChildInfo::_tablename . " e on e.id=d.child_id ");
		$query2->addJoin(" left join " . BaseCatalog::_tablename . " f on f.id = c.catalog_session_id");
		//join the payment
		$query2->addJoin(" left join " . AccountingPaymentInvoice::_tablename . " g on g.accounting_invoice_id = a.id ");
		$query2->addJoin(" left join " . AccountingPayment::_tablename ." h on h.id=g.accounting_payment_id ");
		$query2->addJoin(" left join " . AccountingJournalItemCharge::_tablename . " i on i.accounting_journal_id = h.journal_id and i.charge_id=b.charge_id");
		
		$query2->addJoin(" left join ". AccountingJournal::_tablename . " n on n.id=h.journal_id ");

		$query2->addJoin(" left join " . Payer::_tablename ." j on j.id = a.payer_id ");
		$query2->addJoin(" left join " . PayerType::_tablename ." k on k.id = j.payer_type_id");
		$query2->addJoin(" left join " . RegistrationCosponsor::_tablename ." l on l.registration_id = c.id and a.id=l.accounting_invoice_id ");
		$query2->addJoin(" left join ". MiscellaneousCharge::_tablename ." ch on ch.id = b.charge_id ");
		$query2->addWhere(" a.id =  ".$invoice_id ." and b.amount > 0 and n.cancelled_date is null ");
		//$query2->addGroupby(" a.id,b.registration_id ");
		$query2->addGroupby("b.id, b.registration_id");
		
		//echo $query2->createSQL();die();
		//echo '(' . $query->createSQL() .') union all ('. $query2->createSQL() .')';die();
		$result = $db->executeQuery('(' . $query->createSQL() .') union all ('. $query2->createSQL() .')');
		
		return $result;
	}
	
	
	function getReceipt($receiptid){
	
		Zend_Loader::loadClass("AccountingCreditAccount");
		Zend_loader::loadClass("AccountingPayment");
		Zend_Loader::loadClass("AccountingPaymentCard");
		Zend_Loader::loadClass("AccountingPaymentCash");
		Zend_Loader::loadClass("AccountingJournal");
		Zend_Loader::loadClass("AccountingJournalItemCharge");
		Zend_Loader::loadClass("AccountingJournalNotesView");
		Zend_loader::loadClass("AccountingJournalRegistration");
		Zend_Loader::loadClass("AccountingPaymentInvoice");
		Zend_Loader::loadClass("AccountingRefund");
		Zend_Loader::loadClass("BaseCatalog");
		Zend_loader::loadClass("ChildInfo");
		Zend_Loader::loadClass("MiscellaneousCharge");
		Zend_Loader::loadClass("Payer");
		Zend_Loader::loadClass("PayerCard");
		Zend_Loader::loadClass("PayerCardType");
		Zend_Loader::loadClass("PayerType");
		Zend_loader::loadClass("Registration");
		Zend_Loader::loadClass("RegistrationAdditionalCost");
		Zend_loader::loadClass("RegistrationChild");
		Zend_Loader::loadClass("RegistrationMonth");
		
		
		$query  = new QueryCreator();
		$query->addSelect(" f.session_id,concat(f.name,' ',if(p.name is not null and b.id is null,concat('(',p.name,')'),''),if(q.month = 0,' - Reg. Fee',concat(' - ',monthname(concat(q.year,'-',q.month,'-01')),' ',q.year)),if(rac.id > 0,' ( Additional Cost ) ','') ) as care_session,e.firstname as c_firstname,e.lastname as c_lastname,e.middlename as c_middlename,ifnull((select sum(amount) from ". AccountingInvoice::_tablename." ai left join " . AccountingJournalRegistration::_tablename ." ajr on ajr.accounting_journal_id = ai.journal_id where ajr.accounting_invoice_id=b.accounting_invoice_id and ajr.registration_id=b.registration_id and ajr.registration_month_id=b.registration_month_id),(select sum(amount) from ". AccountingInvoice::_tablename ." ai2 left join ". AccountingJournalItemCharge::_tablename ." aji on aji.accounting_journal_id=ai2.journal_id where aji.charge_id=o.charge_id and aji.registration_id=o.registration_id  )) as cost,ifnull(b.amount,o.amount) as amount_paid");
		$query->addSelect("g.firstname as p_firstname,g.lastname as p_lastname,a.payer_id");
		$query->addSelect("g.address,g.city,g.state,g.zip,h.value as payer_type");
		$query->addSelect(" CASE when i.id>0 then concat(upper(l.value),' ',concat(repeat('X',length(k.card_number)-4),'-',right(k.card_number,4))) when (j.check_number is not null and j.check_number !='') then concat('Check # ',j.check_number)  WHEN m.id > 0 then 'Credit on Account ' when  (j.check_number = '' and j.check_number = '' ) Then 'CASH' END as ptype,date_format(n.transaction_date,'%m/%d/%Y %h:%i %p') as payment_date");
		$query->addSelect(" (select sum(ajr.amount) from ". AccountingRefund::_tablename ." ar LEFT JOIN ". AccountingJournalRegistration::_tablename ." ajr on ar.journal_id = ajr.accounting_journal_id LEFT JOIN ". AccountingJournal::_tablename ." aj on aj.id=ar.journal_id WHERE ar.accounting_payment_id = a.id and b.registration_id=ajr.registration_id and b.registration_month_id=ajr.registration_month_id and b.accounting_invoice_id = ajr.accounting_invoice_id  and aj.cancelled_date is null ) as amount_refunded ");
		$query->addSelect(" if(c.show_notes_to_receipt = 1,c.notes,'') as regNotes ");
		$query->addSelect(" (	SELECT in_j.notes FROM  ". AccountingRefund::_tablename ." in_r LEFT JOIN ". AccountingJournal::_tablename ." in_j ON in_j.id = in_r.journal_id WHERE in_r.accounting_payment_id = ".$receiptid." LIMIT 1) as refund_notes");
		$query->addSelect(" ( SELECT in_ajnv.show_notes_on FROM ". AccountingRefund::_tablename ." in_r LEFT JOIN ". AccountingJournalNotesView::_tablename ." in_ajnv ON in_ajnv.accounting_journal_id = in_r.journal_id WHERE in_r.accounting_payment_id = ".$receiptid." LIMIT 1) as refund_notes_show_on");
		$query->addSelect(" ( SELECT in_j.notes FROM ". AccountingPayment::_tablename ." in_p LEFT JOIN ". AccountingJournal::_tablename ." in_j ON in_j.id = in_p.journal_id WHERE in_p.id = ".$receiptid." LIMIT 1) as payment_notes");
		$query->addSelect(" (	SELECT in_ajnv.show_notes_on FROM ". AccountingPayment::_tablename ." in_p LEFT JOIN ". AccountingJournalNotesView::_tablename ." in_ajnv ON in_ajnv.accounting_journal_id = in_p.journal_id WHERE in_p.id = ".$receiptid." LIMIT 1) as payment_notes_show_on,a.journal_id");
		//$query->addSelect(" n.notes as journal_notes");
		//$query->addSelect(" ajnv.show_notes_on as journal_notes_show");
		$query->addFrom(AccountingPayment::_tablename ." a ");
		$query->addJoin(" left join ". AccountingPaymentInvoice::_tablename ." a2 on a2.accounting_payment_id=a.id ");
		$query->addJoin(" left join ". AccountingJournalRegistration::_tablename ." b on a.journal_id=b.accounting_journal_id and b.accounting_invoice_id=a2.accounting_invoice_id");
		$query->addJoin(" left join ". AccountingJournalItemCharge::_tablename ." o on o.accounting_journal_id = a.journal_id");
		$query->addJoin(" left join ". Registration::_tablename ." c on c.id = ifnull(b.registration_id,o.registration_id) ");
		$query->addJoin(" left join ". RegistrationChild::_tablename ." d on d.registration_id=c.id");
		$query->addJoin(" left join ". ChildInfo::_tablename ." e on e.id=d.child_id ");
		$query->addJoin(" left join ". BaseCatalog::_tablename ." f on f.id=c.catalog_session_id ");
		$query->addJoin(" left join ". Payer::_tablename ." g on g.id=a.payer_id");
		$query->addJoin(" left join ". PayerType::_tablename ." h on h.id=g.payer_type_id");
		$query->addJoin(" left join ". AccountingPaymentCard::_tablename ." i on i.accounting_payment_id=a.id ");
		$query->addJoin(" left join ". AccountingPaymentCash::_tablename ." j on j.accounting_payment_id=a.id");
		$query->addJoin(" left join ". PayerCard::_tablename ." k on k.id=i.payer_card_id");
		$query->addJoin(" left join ". PayerCardType::_tablename ." l on k.card_type_id=l.id ");
		$query->addJoin(" left join ". AccountingCreditAccount::_tablename ." m on m.journal_id=a.journal_id ");
		$query->addJoin(" left join ". AccountingJournal::_tablename ." n on n.id=a.journal_id ");
		$query->addJoin(" left join ". MiscellaneousCharge::_tablename . " p on p.id=o.charge_id");
		$query->addJoin(" left join ". RegistrationMonth::_tablename . " q on q.id=b.registration_month_id");
		$query->addJoin(" LEFT JOIN ". RegistrationAdditionalCost::_tablename ." rac on rac.accounting_payment_id = a.id ");
		//$query->addJoin(" LEFT JOIN ". AccountingJournalNotesView::_tablename ." ajnv ON ajnv.accounting_journal_id = n.id");
		$query->addWhere(" a.id = ". $receiptid);
		
		//echo $query->createSQL();
		$db = DBCon::instance();
		$result = $db->executeQuery($query->createSQL());
		//echo $query->createSQL();
		return $result;
		
	}
	
	
	public static function generatePayment($paymentType , $payerId , $cibAmount = 0 , $payableAmount = 0, $coaAmount = 0 , $payer_card_id = null,$cheknumber = null, $checkisfrom = null , $receiptnumber = null ,$paymentBreakdown = null,$paymentCharges = null,$creditAccountOnly=0,$family_account_id=0,$paymentdate='',$operation_xid = 0,$notes="")
	{
		
		
		Zend_Loader::loadClass("AccountingPayment");
		Zend_Loader::loadClass("AccountingPaymentCash");
		Zend_Loader::loadClass("AccountingPaymentCard");
		Zend_Loader::loadClass("AccountingCreditAccount");
		Zend_Loader::loadClass("AccountingJournalRegistration");
		Zend_Loader::loadClass("AccountingJournalItemCharge");
		Zend_Loader::loadClass("AuthUtility");
		Zend_Loader::loadClass("FamilyAccountPayer");
		Zend_Loader::loadClass("Registration");
		Zend_loader::loadClass("AccountingPaymentInvoice");
		Zend_loader::loadClass("Payer");
		Zend_loader::loadClass("PayerContact");
		Zend_loader::loadClass("ContactTypes");
		Zend_loader::loadClass("PayerCard");
		Zend_loader::loadClass("CardPaymentUtility");
		
		
		$accountCodes = new AccountCodes();
		$accountCodesArray = $accountCodes->getAccountCodesArray("short_name", "account_code");
		$getFamilyAccount = new FamilyAccountPayer();
		$getFamilyAccount->loadWhere('payer_id = '. $payerId);
		
		

		$family_id = 0;
		$accountingJournal = new AccountingJournal();
		if ($creditAccountOnly ==0){
			$accountingJournal->transaction_type = CodesUtility::TRANS_TYPE_PAYMENT;
		} else{
			$accountingJournal->transaction_type = CodesUtility::TRANS_TYPE_CREDIT_ON_ACCOUNT;
		}
		
		if($family_account_id==0){
		
			if($getFamilyAccount->family_account_id > 0) {
				$family_id = $getFamilyAccount->family_account_id;
			} else {
				foreach($paymentBreakdown as $regid => $amount){
					$this_regid = explode(';',$regid);
					$reg_id = $this_regid[0];
				}
				
				$getFamilyid = new Registration();
				$getFamilyid->loadWhere(" id = ". $reg_id);
				$family_id = $getFamilyid->family_account_id;
			}
		} else {
			$family_id = $family_account_id;
		}
		if($paymentdate==''){
			$accountingJournal->transaction_date = TimeUtility::convertToSQLDateFormat(date('m/d/Y H:i:s'),true);
		} else {
			$accountingJournal->transaction_date = $paymentdate;
		}
		
		$accountingJournal->posted = 1;
		$accountingJournal->family_account_id=$family_id;
		$accountingJournal->notes = $notes;
		$accountingJournal->save();
		
		
		$paymentJournalId = $accountingJournal->id;
		
		$accountingJournalItem = new AccountingJournalItem();
		
		if ($creditAccountOnly ==0){
		
					if($cibAmount > 0){
						$accountingJournalItem->clear();
						$accountingJournalItem->journal_id = $paymentJournalId;
						$accountingJournalItem->account_code =  $accountCodesArray[CodesUtility::ACCOUNT_CODE_CIB];
						$accountingJournalItem->cost = $cibAmount;
						$accountingJournalItem->account_type = CodesUtility::ACCOUNT_TYPE_REGISTRATIONS;
						$accountingJournalItem->accounting_type = CodesUtility::ACCOUNTING_TYPE_DEBIT;
						$accountingJournalItem->save();
					}
					
					
					if($payableAmount){
						$accountingJournalItem->clear();
						$accountingJournalItem->journal_id = $paymentJournalId;
						$accountingJournalItem->account_code = $accountCodesArray[CodesUtility::ACCOUNT_CODE_AR];
						$accountingJournalItem->cost = $payableAmount;
						$accountingJournalItem->account_type = CodesUtility::ACCOUNT_TYPE_REGISTRATIONS;
						$accountingJournalItem->accounting_type = CodesUtility::ACCOUNTING_TYPE_CREDIT;
						$accountingJournalItem->save();
					}
					
					$overPayment = 0;
					$overPayment = $cibAmount - $payableAmount;
					
					
					if($overPayment > 0){
						$accountingJournalItem->clear();
						$accountingJournalItem->journal_id = $paymentJournalId;
						$accountingJournalItem->account_code = $accountCodesArray[CodesUtility::ACCOUNT_CODE_COA];
						$accountingJournalItem->cost = $overPayment;
						$accountingJournalItem->account_type = CodesUtility::ACCOUNT_TYPE_REGISTRATIONS;
						$accountingJournalItem->accounting_type = CodesUtility::ACCOUNTING_TYPE_CREDIT;
						$accountingJournalItem->save();
						
						//save to the accounting_credit_on_account
						$accountingCreditAccount = new AccountingCreditAccount();
						$accountingCreditAccount->payer_id = $payerId;
						$accountingCreditAccount->journal_id = $paymentJournalId;
						$accountingCreditAccount->save();
					}
					
					//payment
					$accountingPayment = new AccountingPayment();
					$accountingPayment->payer_id = $payerId;
					$accountingPayment->payment_type = $paymentType;
					$accountingPayment->receipt_number = "";
					$accountingPayment->journal_id = $paymentJournalId;
					$accountingPayment->last_edited_by_id= AuthUtility::getCurrentUserId();
					$accountingPayment->save();
					
					switch ($paymentType){
						case CodesUtility::PAYMENT_TYPE_CREDIT_CARD :
							$accountingPaymentCard = new AccountingPaymentCard();
							$accountingPaymentCard->payer_card_id = $payer_card_id;
							$accountingPaymentCard->accounting_payment_id = $accountingPayment->id;
							$accountingPaymentCard->operation_xid = $operation_xid;
							$accountingPaymentCard->save();
							break;
						case CodesUtility::PAYMENT_TYPE_CASH_OR_CHECK :
							$accountingPaymentCash = new AccountingPaymentCash();
							$accountingPaymentCash->accounting_payment_id = $accountingPayment->id;
							$accountingPaymentCash->check_number = $cheknumber;
							$accountingPaymentCash->check_from = $checkisfrom;
							$accountingPaymentCash->receipt_number = $receiptnumber;
							$accountingPaymentCash->save();
							break;
						case CodesUtility::PAYMENT_TYPE_CREDIT_ON_ACCOUNT :
							
							if($coaAmount > 0 ){
								$accountingJournalItem->clear();
								$accountingJournalItem->journal_id = $paymentJournalId;
								$accountingJournalItem->account_code = $accountCodesArray[CodesUtility::ACCOUNT_CODE_COA];
								$accountingJournalItem->cost = $coaAmount;
								$accountingJournalItem->account_type = CodesUtility::ACCOUNT_TYPE_REGISTRATIONS;
								$accountingJournalItem->accounting_type = CodesUtility::ACCOUNTING_TYPE_DEBIT;
								$accountingJournalItem->save();
							}
							
							$accountingCreditAccount = new AccountingCreditAccount();
							$accountingCreditAccount->payer_id = $payerId;
							$accountingCreditAccount->journal_id = $paymentJournalId;
							$accountingCreditAccount->save();
							break;
					}
					//print_r($paymentBreakdown);die();
					$creditOnAccountValue = $coaAmount;
					
					if($paymentBreakdown){
						$accountingjournalreg = new AccountingJournalRegistration();
							$oldinvoice = array();
							$invoice_new = new AccountingPaymentInvoice();
							
							foreach($paymentBreakdown as $regid => $reg){
								//print_r($reg);
								$registration_month_id = 0;
								foreach($reg as $key => $regvalue) {
									
									$invoice_id = $regvalue['accounting_invoice_id'];
									$registration_id = $regvalue['registration_id'];
									//echo $registration_month_id;
									$registration_month_id = $regvalue['registration_month_id'];
									$amount = 0;
									//echo $creditOnAccountValue;
									if($coaAmount > 0) {
										if($creditOnAccountValue >= $regvalue['amount']) {
											$amount = $regvalue['amount'];
										} else {
											$amount = $creditOnAccountValue;
										}
									
										$creditOnAccountValue = $creditOnAccountValue - $regvalue['amount'];
										
									} else {
										$amount = $regvalue['amount'];
									}
								//	echo '<br>'.$regvalue['registration_month_id']. ' - '.$amount;
									
									if(!in_array($invoice_id,$oldinvoice)) {			
										$invoice_new->clear();
										$invoice_new->accounting_payment_id = $accountingPayment->id;
										$invoice_new->accounting_invoice_id = $invoice_id;
										$invoice_new->save();  
									}
									array_push($oldinvoice,$invoice_id);	
									//if($amount > 0 ){
										$accountingjournalreg->clear();
										$accountingjournalreg->accounting_journal_id = $accountingJournal->id;
										$accountingjournalreg->registration_id = $registration_id;
										$accountingjournalreg->registration_month_id = $registration_month_id ? $registration_month_id : 0;
										$accountingjournalreg->accounting_invoice_id = $invoice_id;
										$accountingjournalreg->amount = $amount;
										$accountingjournalreg->save();
									//}
								}
							}
					}
					//die();
					
					if($paymentCharges){	
						$charges = new AccountingJournalItemCharge();
						$invoice_new = new AccountingPaymentInvoice();
						$oldinvoice = array();
						foreach ($paymentCharges as $chargeid => $amount){
							$split = explode(';',$chargeid);
							
							$invoice_ids = explode('|',$split[1]);
							$invoice_id = $invoice_ids[1];
							
							
							if(!in_array($invoice_id,$oldinvoice)) {			
								$invoice_new->clear();
								$invoice_new->accounting_payment_id = $accountingPayment->id;
								$invoice_new->accounting_invoice_id = $invoice_id;
								$invoice_new->save();  
							}
							array_push($oldinvoice,$invoice_id);	
							
							$charges->clear();
							$charges->accounting_journal_id = $accountingJournal->id;
							$charges->charge_id = $invoice_ids[2];
							$charges->registration_id = $split[0];
							$charges->accounting_invoice_id = $invoice_id;
							$splitamount = explode(',',$amount);
							$charges->amount = $splitamount[0];
							$charges->child_id=$splitamount[1];
							$charges->save();
						}
						
					}
		} else {
		
			if($coaAmount > 0 ){
			
				$accountingCreditAccount = new AccountingCreditAccount();
				$accountingCreditAccount->payer_id = $payerId;
				$accountingCreditAccount->journal_id = $paymentJournalId;
				$accountingCreditAccount->save();
			
				$accountingJournalItem->clear();
				$accountingJournalItem->journal_id = $paymentJournalId;
				$accountingJournalItem->account_code =  $accountCodesArray[CodesUtility::ACCOUNT_CODE_CIB];
				$accountingJournalItem->cost = $coaAmount;
				$accountingJournalItem->account_type = CodesUtility::ACCOUNT_TYPE_REGISTRATIONS;
				$accountingJournalItem->accounting_type = CodesUtility::ACCOUNTING_TYPE_DEBIT;
				$accountingJournalItem->save();
			
				$accountingJournalItem->clear();
				$accountingJournalItem->journal_id = $paymentJournalId;
				$accountingJournalItem->account_code = $accountCodesArray[CodesUtility::ACCOUNT_CODE_COA];
				$accountingJournalItem->cost = $coaAmount;
				$accountingJournalItem->account_type = CodesUtility::ACCOUNT_TYPE_REGISTRATIONS;
				$accountingJournalItem->accounting_type = CodesUtility::ACCOUNTING_TYPE_CREDIT;
				$accountingJournalItem->save();
				
				$accountingPayment = new AccountingPayment();
				$accountingPayment->payer_id = $payerId;
				$accountingPayment->payment_type = $paymentType;
				$accountingPayment->receipt_number = "";
				$accountingPayment->journal_id = $paymentJournalId;
				$accountingPayment->last_edited_by_id= AuthUtility::getCurrentUserId();
				$accountingPayment->save();
				
				if ($payer_card_id){
							$accountingPaymentCard = new AccountingPaymentCard();
							$accountingPaymentCard->payer_card_id = $payer_card_id;
							$accountingPaymentCard->accounting_payment_id = $accountingPayment->id;
							$accountingPaymentCard->operation_xid = $operation_xid;
							$accountingPaymentCard->save();
							
				} else {
							$accountingPaymentCash = new AccountingPaymentCash();
							$accountingPaymentCash->accounting_payment_id = $accountingPayment->id;
							$accountingPaymentCash->check_number = $cheknumber;
							$accountingPaymentCash->check_from = $checkisfrom;
							$accountingPaymentCash->receipt_number = $receiptnumber;
							$accountingPaymentCash->save();
							
				}
			}
		}
	
		
		
		return $accountingPayment->id;
		
	}
	
	
	
	public static function transactCreditCardPayment($amount , $payerId, $payer_card_id)
	{
		Zend_Loader::loadClass("CodesUtility");
		Zend_Loader::loadClass("Payer");
		Zend_Loader::loadClass("ContactTypes");
		Zend_Loader::loadClass("PayerContact");
		Zend_Loader::loadClass("CardPaymentUtility");
		Zend_Loader::loadClass("PayerCard");
		Zend_Loader::loadClass("PayerCardData");
		Zend_Loader::loadClass("AuthUtility");
		Zend_Loader::loadClass("User");
		
		
		//prepare payer and card info
		$payerInfo = new Payer();
		$payerInfo->loadById($payerId);
		
		$payer->id = $payerInfo->id;
		$payer->last_name  = $payerInfo->lastname;
		$payer->first_name = $payerInfo->firstname;
		$payer->zip = $payerInfo->zip;
		$payer->city = $payerInfo->city;
		$payer->country = 'US';
		$payer->state_code = $payerInfo->state;
		$payer->address = $payerInfo->address;
		
		$contactTypes = new ContactTypes();
		$contactTypesArray = $contactTypes->getContactTypesArray("code", "id");
		
		$payerContact = new PayerContact();
		
		$payerContact->loadByPayerIdContactTypeId($payerId , $contactTypesArray[CodesUtility::CONTACT_TYPE_EMAIL]);
		$payer->email = $payerContact->value;
		
		$payerContact->loadByPayerIdContactTypeId($payerId , $contactTypesArray[CodesUtility::CONTACT_TYPE_DAY_PHONE]);
		$payer->contact_phone = $payerContact->value;
		
		$payerCard = new PayerCard();
		$payerCard->loadById($payer_card_id);
		
		$userid = AuthUtility::getCurrentUserId();
		$oUser = new User();
		$oUser->loadById($userid);
		$cckey = $oUser->cc_key;
		//echo $cckey;
		//die();
		
		
		$payerCardData = new PayerCardData();
		$ccNumber = $payerCardData->getPayerCardNumber($payer_card_id, AuthUtility::getCurrentUserId(), $cckey);
		
		$credit_card->card_type = $payerCard->CardType->value;
		$credit_card->cc_number = str_replace(" ","",$ccNumber);
		$credit_card->expiration_month = date("m", strtotime($payerCard->expiration));
		$credit_card->expiration_year = date("Y", strtotime($payerCard->expiration));
		$credit_card->code = $payerCard->cbd_number;
		
		$card_data->payer = $payer;
		$card_data->credit_card = $credit_card;
		
		$client_session = new Zend_Session_Namespace(SESSION_CLIENT);
		$result = CardPaymentUtility::payByCreditCard($card_data, $amount, $client_session->config, false);
		
		
		return $result;
	}
	
	
	
	public static function getAvailableCreditOnAccount($familyAccountId){
		
		Zend_Loader::loadClass("QueryCreator");
		Zend_Loader::loadClass("AccountingJournalItem");
		Zend_Loader::loadClass("AccountingJournal");
		Zend_Loader::loadClass("AccountingPayment");
		Zend_Loader::loadClass("FamilyAccountPayer");
		Zend_Loader::loadClass("AccountCode");
		
		if($familyAccountId){
			
			$query = new QueryCreator();
			$query->addSelect(" sum(if(aji.accounting_type = 2, aji.cost , -1*aji.cost)) cost ");
		    $query->addFrom( AccountingJournalItem::_tablename . " as aji ");
			$query->addJoin( " INNER JOIN " . AccountingJournal::_tablename . " as aj ON aj.id = aji.journal_id "  );
			$query->addJoin( " INNER JOIN " . AccountingPayment::_tablename . " as ap ON ap.journal_id = aji.journal_id "  );
			$query->addJoin( " INNER JOIN " . FamilyAccountPayer::_tablename . " as fap ON fap.payer_id = ap.payer_id "  );
			$query->addJoin( " INNER JOIN " . AccountCode::_tablename . " as ac ON ac.id = aji.account_code"  );
			$query->addWhere(" ac.short_name = '". CodesUtility::ACCOUNT_CODE_COA."' AND fap.family_account_id =  ". $familyAccountId);
			$query->addgroupBy(" fap.family_account_id ");
			
			$db = DBCon::instance();
			$result = $db->executeQuery($query->createSQL());
			
			if($result){
				$amount = $result[0]['cost'];
				return $amount;
			}
			
		}
		
		return 0;
		
	}
	
	public function paymentAttachment(
		$receiptid, 
		$journalId = 0,
		$public = true){
		
		Zend_loader::loadClass("PaymentUtility");
		Zend_loader::loadClass("PayerContact");
		Zend_loader::loadClass("ContactTypes");
		Zend_loader::loadClass("GeneralSettings");
		Zend_Loader::loadClass("BaseView");
		Zend_Loader::loadClass("SetupUtility");
		
		$clientView = new BaseView();
		$clientView->setLayout("popup");
		
		$client = new GeneralSettings();
		$clientValue = $client->getGeneralSettingsArray();
		
		$paymentUtil = new PaymentUtility();
		
		$paymentValues = $paymentUtil->getReceipt($receiptid);
		$payment = 0;
		foreach ($paymentValues as $pmt => $value){
				$payment = $value['amount_paid'] + $payment;
		}
	
		$contactTypes = new ContactTypes();
		$contactTypesArray = $contactTypes->getContactTypesArray("code", "id");
		$contact_type_id = $contactTypesArray[CodesUtility::CONTACT_TYPE_EMAIL];
		
		
		$clientView->assign('pmtValues',$paymentValues);
		$PayerContact = new PayerContact();
		$PayerContact->loadByPayerIdContactTypeId($paymentValues[0]['payer_id'] , $contact_type_id);
		
		/**
		 * modified to include journal notes
		 * @modified by: Stacey K.D. Cardoso
		 * @email: stacey.cardoso@dwebsite.net
		 * @on: 16-April-2009
		 **/
		//print_r("is public", $public);die();
		$clientView->assign(
			"public", $public);
		$clientView->assign(
			"emailed", true);
		$journal = new AccountingJournal();
		$journal->loadById($journalId);
		if($journal) $clientView->assign("notes", $journal->notes);
		
		$journalNotesShow = new AccountingJournalNotesView();
		$journalNotesShow->loadWhere(
			"accounting_journal_id = ".
			$journalId);
		if($journalNotesShow)
		{
			$clientView->assign(
				"notes_view", 
				$journalNotesShow->show_notes_on);
		}
		
		$paymentValues = $paymentUtil->getReceipt($receiptid);
		$payment = 0;
		$refund = 0;
		foreach ($paymentValues as $pmt => $value){
				$payment = $value['amount_paid'] + $payment;
				$refund = $refund + $value['amount_refunded'];
		}
		$clientView->assign("refund",$refund);
		
			//$this->assign("id",$family_account_id);
		$clientView->assign("payerlastname",$paymentValues[0]['p_lastname']);
		$clientView->assign("payerfirstname",$paymentValues[0]['p_firstname']);
		$clientView->assign("payeraddress",$paymentValues[0]['address']);
		$clientView->assign("payercity",$paymentValues[0]['city']);
		$clientView->assign("payerstate",$paymentValues[0]['state']);
		$clientView->assign("payerzip",$paymentValues[0]['zip']);
		$clientView->assign("org_name",$clientValue['ORG_NAME']);
		$clientView->assign("org_add",$clientValue['ORG_ADD']);
		$clientView->assign("org_city",$clientValue['ORG_CITY']);
		$clientView->assign("org_state",$clientValue['ORG_STATE']);
		$clientView->assign("org_zip",$clientValue['ORG_ZIP']); 
		$clientView->assign("org_phone",$clientValue['CONTACT_PHONE']);
		$clientView->assign("org_email",$clientValue['CONTACT_EMAIL']);
		$clientView->assign("org_tax_id_number",$clientValue[SetupUtility::ORGANIZATION_TAX_ID_NUMBER]);			
		$clientView->assign("payment",$payment);
		$clientView->assign("payment_method",$paymentValues[0]['ptype']);
		$clientView->assign("payment_date",$paymentValues[0]['payment_date']);
		$clientView->assign("receiptid",$receiptid);
		$clientView->assign("payeremail",$PayerContact->value);
		
		$clientView->setTemplateDir(DEFAULT_DIR . "views/account/");
		$clientView->setTemplate('receipt.tpl');
		return $clientView->getHTML(); 
		
	
	}
	
	
	
	
	public static function emailPayment(
		$id,
		$subject,
		$body,
		$toemail='',
		$href='',
		$journalId = 0,
		$public = true){
		
			Zend_loader::loadClass("Zend_Mail_Transport_Smtp");
			Zend_loader::loadClass("Zend_Mail");
			Zend_loader::loadClass("CodesUtility");
			Zend_loader::loadClass("Administrator");
			Zend_loader::loadClass("AuthUtility");
			Zend_Loader::loadClass("Payer");
			Zend_Loader::loadClass("AccountingPayments");
			Zend_Loader::loadClass("GeneralSettings");
			Zend_Loader::loadClass("SetupUtility");
			Zend_Loader::loadClass("SessionProgramNotification");
			
			
			$pnotification = new SessionProgramNotification();
			$userid = AuthUtility::getCurrentUserId();
			$admin = new Administrator();
			$admin->loadWhere("id = ". $userid);
			$email = $admin->email;
			if(empty($email)){
				$email = CodesUtility::INV_MAILER;
			}
			
		
			$settings = new GeneralSettings();
			$settings->load();
			$settingvalues = $settings->toArray();
			
			if (!empty($settingvalues))
			{
				foreach($settingvalues as $item)
				{
					switch($item["code"])
					{
						case SetupUtility::ORGANIZATION_NAME:
							$org_name = $item["value"];
							break;
						case SetupUtility::CONTACT_EMAIL:
							$contact_email = $item["value"];
							break;
						case SetupUtility::CONTACT_PHONE:
							$contact_phone = $item["value"];
							break;
						case SetupUtility::CONTACT_NAME:
							$contact_name = $item["value"];
							break;
					}
				}
			}	
			
			$body = urldecode($body);
			$from = $email;
			$transport = new Zend_Mail_Transport_Smtp(CodesUtility::SMTP,array('port' => CodesUtility::SMTP_PORT));
	        Zend_Mail::setDefaultTransport($transport);
			
			if($id) {
				$attachment = PaymentUtility::paymentAttachment(
					$id, 
					$journalId,
					$public);
			} else {
				$attachment = '';
			}
			
	        $mailer = new Zend_Mail();
			$at = $mailer->createAttachment($attachment);
			$at->type = 'html';
			$at->filename =  'payment.html';
			
			$accountingPayment = new AccountingPayments();
			$aValues = $accountingPayment->getByProgram($id);
			$reg = '';
			$line = 0;
			$notification = array();
			$program = array();
			
			foreach($aValues as $p => $value){
				$line = $line + 1;
				$reg = $reg.'<br>'. $line.') '.$value['program_name'] . ' - '. $value['session_name'] .' for '. $value['childname'];
				if($value['program_id'] > 0 ) {
					$notification[$line] = $pnotification->getEmailyByProgram($value['program_id']);
					$program[$line] = '<br>'. $value['program_name'] . ' - '. $value['session_name'] .' for '. $value['childname'];
				}
			}
			$heading = '<font face="verdana,arial,helvetica">';
			$heading .= 'Hi '. $aValues[0]['payername'] .',<br>';
			$heading .= 'Attached is a payment confirmation for '. $reg . ' for your records. <br><br>';
			$heading .= 'Or Please click on the link below to view the Receipt online from your Family Account. You will need to login first.';
			$heading .= '<br><br>If you have questions, please contact us.';
			$heading .= '<br><br>Sincerely,<br><br><br>';
			$heading .= $contact_name."<br>";
			$heading .= $org_name."<br>";
			$heading .= $contact_phone."<br>";
			$heading .= $contact_email."<br><br>";
			
			$client_session = new Zend_Session_Namespace(SESSION_CLIENT);
			$config = $client_session->config;
			
			$heading .= "<a href='".$config->client->host ."'>CLICK HERE</a> to login to your Family Account to view Payment Information";
			
			$subject = 'Payment Confirmation for '.  $aValues[0]['session_name'] .' from ' .$org_name;
			$heading .= '</font>';
						
			$mailer->setBodyHTML($heading);
	        $mailer->setFrom($from);
	        $mailer->addTo($toemail);
	        $mailer->setSubject($subject);
			try{
				$mailer->send();
			}catch(Exception $e){
				$logger = new BaseLogger();
				$logger->logError("Fail sending Email.. ".$e->getMessage()." \n ".$e->getTraceAsString()."\n");
			}
			
			
			/*  SEND  CONFIRMATION */
			if(count($notification)) {
				foreach($notification as $nkey => $nvalue) {
					$mailer_confirmation = new Zend_Mail();
					$mailer_confirmation->setBodyHTML("Payment notifcation is sent to :". $toemail." <br> With the Following Session Registered: <br>". $program[$nkey]);
					$mailer_confirmation->setFrom($email);
					$mailer_confirmation->addTo($nvalue);
					$mailer_confirmation->setSubject("Payment Confirmation Sent");
					try {
						$mailer_confirmation->send();
					} catch(Exception $e){
						$logger = new BaseLogger();
						$logger->logError("Fail sending Email.. ".$e->getMessage()." \n ".$e->getTraceAsString()."\n");
					}
				}
			}
		}
	
	public function generateRefund($user_id,$family_account_id,$payment=array(),$refundall = 0,$type="R",$doctype="I",$cancel=0,$fromtab='',$refundNotes,$showNotesIn = array()){
		
		/**
		 * Modified so that note 
		 *	display options are included.
		 *
		 * @modified by: Stacey K.D. Cardoso 
		 * @email: stacey.cardoso@dwebsite.net
		 * @on: 1-April-2009
		 **/
	
		Zend_Loader::loadClass("AccountCodes");
		Zend_loader::loadClass("AccountingJournal");
		Zend_loader::loadClass("AccountingJournalItem");
		Zend_loader::loadClass("AccountingJournalItemCharge");
		Zend_loader::loadClass("AccountingJournalItemCharges");
		Zend_Loader::loadClass("AccountingJournalNotesView");
		Zend_Loader::loadClass("AccountingJournalRegistration");
		Zend_Loader::loadClass("AccountingJournalRegistrations");
		Zend_Loader::loadClass("AccountingPayment");
		Zend_loader::loadClass("AccountingPaymentCard");
		Zend_Loader::loadClass("AccountingRefund");
		Zend_loader::loadClass("AuthUtility");
		Zend_loader::loadClass("CardPaymentUtility");
		Zend_loader::loadClass("CodesUtility");
		Zend_loader::loadClass("ContactTypes");
		Zend_loader::loadClass("Payer");
		Zend_loader::loadClass("PayerCard");
		Zend_loader::loadClass("PayerCardData");
		Zend_loader::loadClass("PayerContact");
		Zend_loader::loadClass("TimeUtility");
		
		$accountCodes = new AccountCodes();
		$accountCodesArray = $accountCodes->getAccountCodesArray("short_name", "account_code");
		
		$registrationArray = array();
	
		$paymentid = array();
		$newrefund = new AccountingRefund();
		$newJournalReg = new AccountingJournalRegistration();
		$newJornalCharge = new AccountingJournalItemCharge();
		
		$accountingPaymentCard = new AccountingPaymentCard();
		$payerInfo = new Payer();
		
		$total_amount  = 0;
		$journal_id = 0;
		foreach($payment as $key => $value){
			
			//get accounting_payment_cards record using $value['payment_id']
			//from accounting_payment_cards record.. get operation_xid
			//get payer and card data
			//perform cc credit transaction
			//if fail continue;
			//if success.. save operation_xid
			$accountingPaymentCard->clear();
			$hasCardPayment = 0;
			$newOperation_xid = "";
			$hasCardPayment = $accountingPaymentCard->loadByAccountingPaymentId($value['payment_id']);
			if($hasCardPayment){
			
				// process refund on credit card if not leave on account
				If($type=='R'){ 
					$oldOperation_xid = $accountingPaymentCard->operation_xid;
				
					unset($card_data);
					unset($payer);
					unset($credit_card);
					unset($payment);
					//prepare payer and card info
					$payerInfo->clear();
					$payerInfo->loadById($value['payer_id']);
					$payer->id = $payerInfo->id;
					$payer->last_name  = $payerInfo->lastname;
					$payer->first_name = $payerInfo->firstname;
					$payer->zip = $payerInfo->zip;
					$payer->city = $payerInfo->city;
					$payer->state_code = $payerInfo->state;
					$payer->country = 'US';
					$payer->address = $payerInfo->address;
					
					$contactTypes = new ContactTypes();
					$contactTypesArray = $contactTypes->getContactTypesArray("code", "id");
					
					$payerContact = new PayerContact();
					
					$payerContact->loadByPayerIdContactTypeId($value['payer_id'] , $contactTypesArray[CodesUtility::CONTACT_TYPE_EMAIL]);
					$payer->email = $payerContact->value;
					
					$payerContact->loadByPayerIdContactTypeId($value['payer_id'] , $contactTypesArray[CodesUtility::CONTACT_TYPE_DAY_PHONE]);
					$payer->contact_phone = $payerContact->value;
					
				 	$payerCard = new PayerCard();
					$payerCard->loadById($accountingPaymentCard->payer_card_id);
					
					$payerCardData = new PayerCardData();
					$ccNumber = $payerCardData->getPayerCardNumber($accountingPaymentCard->payer_card_id, AuthUtility::getCurrentUserId(), AuthUtility::getCurrentCcKey());
					
					$credit_card->card_type = $payerCard->CardType->value;
					$credit_card->cc_number = $ccNumber;
					$credit_card->expiration_month = date("m", strtotime($payerCard->expiration));
					$credit_card->expiration_year = date("Y", strtotime($payerCard->expiration));
					$credit_card->code = $payerCard->cbd_number;
					
					$card_data->payer = $payer;
					$card_data->credit_card = $credit_card;
					
					$payment->operation_xid = $oldOperation_xid;
					//$payment->transaction_date is needed if using orbital
					
					$card_data->payment = $payment;
				
					$client_session = new Zend_Session_Namespace(SESSION_CLIENT);
					$result = CardPaymentUtility::payByCreditCard($card_data, $value['amount'] , $client_session->config, true);
					if($result->code == CodesUtility::TRANSACTION_CODE_SUCCESS){
						$newOperation_xid = $result->operation_xid;
					}else{
						//return $result->msg;
						continue;
					}
				}
				
			}
			
			if(!$journal_id){
				$newjournal = new AccountingJournal();
				$journal_id = $newjournal->newJournal(
					$family_account_id,
					CodesUtility::TRANS_TYPE_REFUND,
					TimeUtility::convertToSQLDateFormat(date('m/d/Y H:i:s'),true),
					1,
					$refundNotes);
			}
			else
			{
				$journal = new AccountingJournal();
				$journal->loadById($journal_id);
				$journal->notes = $refundNotes;
				$journal->save();
			}
			
			if($refundNotes && !empty($showNotesIn))
			{
				$notesView = new AccountingJournalNotesView();
				$notesView->clear();
				$notesView->accounting_journal_id = $journal_id;
				$notesView->show_notes_on = "";
				
				foreach($showNotesIn as $show)
				{
					//PRINTED_RECEIPT, EMAIL_RECEIPT, PUBLIC_VIEW
					if($show != CodesUtility::PUBLIC_INVOICE && 
							$show != CodesUtility::EMAIL_RECEIPT &&
							$show != CodesUtility::PRINTED_RECEIPT)
						continue;
					
					if(strlen($notesView->show_notes_on) > 0)
						$notesView->show_notes_on .= ",";
					
					$notesView->show_notes_on .= $show;
				}
				
				if(strlen($notesView->show_notes_on) > 0)
					$notesView->save();
			}
			
			
			$newrefund->newRefund(
				$journal_id,
				$value['payer_id'],
				$user_id,
				$value['payment_id'],
				$newOperation_xid);
				
			$registrationArray[] = $value['registration_id'];
			
			$newrefund->clear();
			$payments = new AccountingPayment();
			$payments->loadWhere(" id = ". $value['payment_id']);
			$refundAmount = $value['amount']; 
			if($doctype=="I" || $doctype == "i"){
			  
				$regitems = new AccountingJournalRegistrations();
				$regitems->loadWhere("accounting_journal_id = ". $payments->journal_id . " and accounting_invoice_id = ". $value['accounting_invoice_id'] );
				//print_r($regitems->toArray());die();
				foreach($regitems->toArray() as $key => $regitemValue){ 
        
					if($refundAmount >= $regitemValue['amount']) {
						$detailAmount = $regitemValue['amount'];
					} else {
						$detailAmount = $refundAmount;
					}	
					if($detailAmount > 0 ) { 
            
						$newJournalReg->newJournalRegistration($journal_id,$regitemValue['registration_id'],$regitemValue['registration_month_id'],$regitemValue['accounting_invoice_id'],$detailAmount,0);
						$newJournalReg->clear();
					}
					$refundAmount = $refundAmount - $regitemValue['amount'];
				}
			
			} else {
			  $regitems = new AccountingJournalItemCharges();  
			  $regitems->loadWhere("accounting_journal_id = ". $payments->journal_id . " and accounting_invoice_id = ". $value['accounting_invoice_id']);
			  foreach($regitems->toArray() as $key => $regitemValue){ 
  
				if($refundAmount >= $regitemValue['amount']) {
					$detailAmount = $regitemValue['amount'];
				} else {
					$detailAmount = $refundAmount;
				}
				if($refundAmount > 0 ) { 
				      $newJornalCharge->newJournalRegistration($journal_id,$regitemValue['registration_id'],$regitemsValue['accounting_invoice_id'],$detailAmount,0,$regitemValue['charge_id'],$regitemValue['child_id']);
				      $newJornalCharge->clear();
				   }
				  $refundAmount = $refundAmount - $regitemValue['amount'];
				} 
			}
			$newJournalReg->clear();
			$total_amount = $total_amount + $value['amount'];
		}
		
		if($journal_id){
			$newJournalItem = new AccountingJournalItem();
			#Debit side sales / Ar
			if($fromtab == 'payment') {
				$account = $accountCodesArray[CodesUtility::ACCOUNT_CODE_AR]; 
			} else {
				$account = $accountCodesArray[CodesUtility::ACCOUNT_CODE_SALES]; 
			}
			$newJournalItem->newJournalItem($journal_id,$account,$total_amount,CodesUtility::ACCOUNTING_TYPE_DEBIT,2);
				
			#credit side refund
			$newJournalItem->clear();
			If($type=='R'){
				$newJournalItem->newJournalItem($journal_id,$accountCodesArray[CodesUtility::ACCOUNT_CODE_RF],$total_amount,CodesUtility::ACCOUNTING_TYPE_CREDIT,2);
			} ELSE {
				$newJournalItem->newJournalItem($journal_id,$accountCodesArray[CodesUtility::ACCOUNT_CODE_COA],$total_amount,CodesUtility::ACCOUNTING_TYPE_CREDIT,2);
			}	
			
			return $registrationArray;
		}else{
			return false;
		}
		
	}
	
	
	
	public function getRegistrationPayment($registration_id){
	
		Zend_loader::loadClass("AccountingJournal");
		Zend_loader::loadClass("AccountingPayment");
		Zend_loader::loadClass("AccountingJournalRegistration");
		Zend_loader::loadClass("QueryCreator");
		Zend_Loader::loadClass("RegistrationMonth");
		
		$query = new QueryCreator();
		$query->addSelect(" c.registration_id,a.id as payment_id,c.amount,c.accounting_invoice_id,c.registration_month_id,payer_id ");
		$query->addFrom(AccountingPayment::_tablename . " a ");
		$query->addJoin(" left join ". AccountingJournal::_tablename . " b on a.journal_id = b.id");
		$query->addJoin(" left join ". AccountingJournalRegistration::_tablename . " c on c.accounting_journal_id=b.id");
		$query->addJoin(" Left join ". RegistrationMonth::_tablename . " d on d.id=c.registration_month_id");
		$query->addWhere(" c.registration_id = ". $registration_id . " and b.cancelled_date is null ");
		$query->addWhere(" d.month >= month(current_date) and d.year >= year(current_date)  ");
		$query->addOrderBy(" a.id ");
		$db = DBCon::instance();
		$result = $db->executeQuery($query->createSQL());
		
		return $result;
	
	}
	
	public function getListpaymenthistory($user_family_account_id,$user_date_from='',$user_date_to='',$action,$forPublic = false)
	{
		
			Zend_loader::loadClass("AccountingJournal");
			Zend_loader::loadClass("AccountingPayment");
			Zend_loader::loadClass("AccountingPaymentInvoice");
			Zend_loader::loadClass("AccountingPaymentCash");
			Zend_loader::loadClass("AccountingPaymentCard");
			Zend_loader::loadClass("AccountingInvoice");
			Zend_loader::loadClass("AccountingJournalRegistration");
			Zend_loader::loadClass("AccountingJournalItemCharge");
			Zend_Loader::loadClass("AccountingJournalNotesView");
			Zend_loader::loadClass("PayerCard");
			Zend_Loader::loadClass("PayerCardType");
			Zend_loader::loadClass("Registration");
			Zend_loader::loadClass("AccountingJournalItem");
			Zend_Loader::loadClass("RegistrationChild");
			Zend_Loader::loadClass("ChildInfos");
			Zend_Loader::loadClass("AccountingRefund");
			Zend_Loader::loadClass("BaseCatalog");
			Zend_Loader::loadClass("Payer");
			Zend_loader::loadClass('CodesUtility');
			Zend_Loader::loadClass("AccountCodes");
			Zend_Loader::loadClass("FamilyAccountPayer");
			Zend_Loader::loadClass("AccountingCreditAccount");
			Zend_Loader::loadClass("RegistrationCosponsor");
			Zend_Loader::loadClass("PayerType");
			Zend_Loader::loadClass("MiscellaneousCharge");
			Zend_Loader::loadClass("AccountingPaymentRecurring");
			Zend_Loader::loadClass("AccountingPaymentRecurringSetup");
			Zend_Loader::loadClass("AccountingNsfCheck");
			
			$accountCodes = new AccountCodes();
			$accountCodesArray = $accountCodes->getAccountCodesArray("short_name", "account_code");
			
			#invoice
			$query = new QueryCreator();
			$query->addSelect(" a.id as line_id,a.transaction_date as tdate,CASE WHEN k.code = '". CodesUtility::PAYER_TYPE_SPONSOR ."' then concat(j.lastname,', ',if(j.firstname=j.lastname,'',j.firstname),' Co-sponsor Invoice') WHEN k.code = '". CodesUtility::PAYER_TYPE_FAMILY ."' THEN ifnull(concat(if(c.notes = 'Automatic Invoicing','Invoice Auto-generated - ','Auto Invoice - '),if(i.payer_id is null, concat(j.lastname,', ',j.firstname),''),concat(g.lastname,', ',g.firstname)) ,'Miscellaneous Charges') END as description");
			$query->addSelect(" CASE when transaction_type=1 then  'Debit'  when transaction_type=2 then 'Invoice' when transaction_type=3 then 'Refunds' when transaction_type=4 then 'Credit' END as ttype ,CASE when transaction_type=2 then  sum(d.amount) END as invoice_total,CASE when c.invoice_number is not null then concat('Inv #',invoice_number)  END  as ptype,0 as amount_paid,sum(d.amount) as amount_due,0 as credits,0 , c.id as invoiceid,a.family_account_id,'I' as doctype,if(a.cancelled_date is null,'',concat(' Voided ', date_format(a.cancelled_date,'%m/%d/%Y') )) as voided "); 
			$query->addSelect("ifnull((select sum(ajr.amount) from ". AccountingPayment::_tablename ." ap LEFT JOIN ". AccountingJournal::_tablename  ." aj on aj.id=ap.journal_id LEFT JOIN ". AccountingJournalRegistration::_tablename  ." ajr on ajr.accounting_journal_id = ap.journal_id where aj.cancelled_date is null and c.id=ajr.accounting_invoice_id ) - ifnull(( select sum(amount) from ". AccountingRefund::_tablename ." ar LEFT JOIN ". AccountingJournalRegistration::_tablename ." ajr1 on ajr1.accounting_journal_id = ar.journal_id LEFT JOIN ". AccountingJournal::_tablename ." aj1 on aj1.id=ar.journal_id where aj1.cancelled_date is null and c.id=ajr1.accounting_invoice_id ),0) ,0) as with_payment,e.id as registration_id,c.id as mykey,c.id as accounting_invoice_id ");
			$query->addSelect("a.notes as journal_notes");
			$query->addSelect("1 as show_on_family_view");
			$query->addFrom( AccountingJournal::_tablename.' a ');
			$query->addJoin('left join '. AccountingInvoice::_tablename. ' c on c.journal_id=a.id ');
			$query->addJoin('left join '. AccountingJournalRegistration::_tablename. ' d on d.accounting_journal_id=c.journal_id');
			$query->addJoin('left join '. Registration::_tablename .' e on e.id=d.registration_id');
			$query->addJoin('left join '. RegistrationChild::_tablename .' f on f.registration_id=e.id');
			$query->addJoin('left join '. ChildInfo::_tablename .' g on g.id=f.child_id');
			$query->addJoin('left join '. FamilyAccountPayer::_tablename .' i on i.payer_id=c.payer_id and i.family_account_id ='.$user_family_account_id );
			$query->addJoin('left join '. Payer::_tablename .' j on j.id=c.payer_id');
			$query->addJoin('left join '. PayerType::_tablename .' k on j.payer_type_id = k.id ');
			$query->addWhere(' a.transaction_type=2 and a.posted=1 and a.family_account_id = '. $user_family_account_id );
			$query->addHaving(' invoice_total IS NOT NULL ');
			if(!empty($user_date_from)){
				$query->addWhere("  date_format(a.transaction_date,'%Y-%m-%d') between '". $user_date_from ."' and '". $user_date_to ."'");
			}
			//$query->addWhere(' a.cancelled_date is null ');
			$query->addGroupby('a.id,c.id');
			$query->addHaving("invoice_total > 0"); #added by Stacey on 7-April-2009
			
			/**
			 * added so that the misc charge invoices will not be duplicated
			 * in the first query.
			 * (misc charge invoices are in query6)
			 * @modified by: stacey k.d. cardoso
			 * @email: stacey.cardoso@dwebsite.net
			 * @on: 20-april-2009
			 **/
			$query->addJoin('LEFT JOIN '. AccountingJournalItemCharge::_tablename. ' s on s.accounting_journal_id = a.id ');
			$query->addWhere(' s.amount is NULL');
			
			#-- payments
			$query2 = new QueryCreator();
			$query2->addSelect(" all a.journal_id as line_id,transaction_date as tdate,concat(m.firstname,' ', if(m.firstname=m.lastname,' ',m.lastname),' Payment for Invoice #',ifnull(f.invoice_number,'')) as description ");
			$query2->addSelect(" CASE when d.id>0 then 'Debit' when (e.check_number is not null and e.check_number !='') then 'Check' when (e.check_number='' or e.check_number is null or e.id>0) then 'Cash' End as ttype");
			$query2->addSelect(" sum(g.amount) as invoice_total,CASE when d.id>0 then concat(i.value,' ',concat(repeat('X',4),'-',right(h.card_number,4))) when (e.check_number is not null and e.check_number !='') then concat('Check # ',e.check_number)  WHEN (k.id > 0 and d.id is null and e.id is null) then 'Credit on Account ' when (e.id > 0 and (e.check_number is null or e.check_number ='')) Then 'Cash' END as ptype");
			$query2->addSelect("  ifnull( (select sum(j.amount) from ". AccountingJournalRegistration::_tablename ."  as j   where j.accounting_journal_id=a.journal_id and j.accounting_invoice_id=c.accounting_invoice_id ), (select sum(amount) from ". AccountingJournalItemCharge::_tablename ." as j1 where j1.accounting_journal_id=a.journal_id ) ) as amount_paid ");
			
			#amount due  = invoice_total - payment - charges  + refund
			$query2->addSelect(" sum(ifnull(g.amount,l.amount)) - 
			ifnull((select sum(jx.amount) from ". AccountingJournalRegistration::_tablename ."  as jx  left join ". AccountingJournal::_tablename  ." jy on jy.id=jx.accounting_journal_id  left join ". AccountingPayment::_tablename ." jz on jz.journal_id=jy.id left join ". AccountingPaymentInvoice::_tablename ." jw on jw.accounting_payment_id=jz.id where jy.cancelled_date is null  and  jw.accounting_invoice_id=f.id and jy.transaction_date <= b.transaction_date  and jx.accounting_invoice_id = c.accounting_invoice_id),0)
			- ifnull((select sum(j4z.amount) from ". AccountingPayment::_tablename ." j1z left join ". AccountingPaymentInvoice::_tablename ." j2z on j1z.id=j2z.accounting_payment_id left join ". AccountingJournal::_tablename ." j3z on j3z.id=j1z.journal_id left join ". AccountingJournalItemCharge::_tablename ." j4z on j4z.accounting_journal_id = j1z.journal_id and j2z.accounting_invoice_id=j4z.accounting_invoice_id where j3z.cancelled_date is null and j3z.transaction_date <= b.transaction_date and c.accounting_invoice_id = j2z.accounting_invoice_id  ),0)
			+ ifnull((select sum(c1.amount) from ". AccountingRefund::_tablename ." a1 left join ". AccountingJournal::_tablename ." b1 on a1.journal_id=b1.id left join ". AccountingJournalRegistration::_tablename ." c1 on c1.accounting_journal_id=b1.id where c1.registration_id=g.registration_id and b1.cancelled_date is null and b1.transaction_date <= b.transaction_date ),0) as amount_due");
			//$query2->addSelect(" ifnull(sum(g.amount),sum(l.amount)) as amount_due");
			
			$query2->addSelect(" 0 as credits,0,a.id as invoiceid,b.family_account_id,'P' as doctype ");
			$query2->addSelect(" if(b.cancelled_date is null,'',concat( if(ar.id>0,' Refunded ',' Voided '), date_format(b.cancelled_date,'%m/%d/%Y') )) as voided,0 as with_payment,ifnull(g.registration_id,l.registration_id) as registration_id,a.id as mykey,c.accounting_invoice_id");
			$query2->addSelect(" b.notes as journal_notes");
			$query2->addSelect("1 as show_on_family_view");
			$query2->addFrom( AccountingPayment::_tablename." a ");
			$query2->addJoin(" left join ". AccountingJournal::_tablename ." b on a.journal_id=b.id ");
			$query2->addJoin(" left join ". AccountingPaymentInvoice::_tablename." c on a.id=c.accounting_payment_id ");
			$query2->addJoin(" left join ". AccountingPaymentCard::_tablename." d on d.accounting_payment_id=a.id");
			$query2->addJoin(" left join ". AccountingPaymentCash::_tablename." e on e.accounting_payment_id=a.id");
			$query2->addJoin(" left join ". AccountingInvoice::_tablename. " f on f.id = c.accounting_invoice_id ");
			$query2->addJoin(" left join ". AccountingJournalRegistration::_tablename ." g on g.accounting_journal_id=f.journal_id and c.accounting_invoice_id=g.accounting_invoice_id");
			$query2->addJoin(" left join ". AccountingJournalItemCharge::_tablename ." l on l.accounting_journal_id=f.journal_id and l.accounting_invoice_id=c.accounting_invoice_id");
			$query2->addJoin(" left join ". PayerCard::_tablename. " h on h.id=d.payer_card_id");
			$query2->addJoin(" left join ". PayerCardType::_tablename ." i on h.card_type_id=i.id ");
			$query2->addJoin(" left join ". Registration::_tablename ." j on j.id=g.registration_id ");
			$query2->addJoin(" left join ". AccountingCreditAccount::_tablename ." k on k.journal_id = a.journal_id ");
			$query2->addJoin(" left join ". Payer::_tablename . " m on m.id=a.payer_id ");
			$query2->addJoin(" LEFT JOIN ". AccountingRefund::_tablename . " ar on ar.accounting_payment_id = a.id");
			//$query2->addJoin(" LEFT JOIN ". AccountingJournalRegistration::_tablename . " ajr1 on ajr1.accounting_journal_id = ar.journal_id and g.registration_id = ajr1.");
			$query2->addWhere(" b.family_account_id = ".$user_family_account_id ." and f.invoice_number is not null ");
			$query2->addHaving(' invoice_total IS NOT NULL and amount_paid > 0 ');
			if(!empty($user_date_from)){
				$query2->addWhere("  date_format(b.transaction_date,'%Y-%m-%d') between '". $user_date_from ."' and '". $user_date_to ."'");
			}
			$query2->addGroupby(" a.id,f.id");
			//echo $query2->createSQL();
			
			
			# -- credit on account
			$query3 = new QueryCreator();
			$query3->addSelect(" all a.id as line_id,transaction_date as tdate,'Credit on Account' as description,'Credit'	 as ttype,0 as invoice_total,CASE when c.id>0 then concat(j.value,' ',concat(repeat('X',length(i.card_number)-4),'-',right(i.card_number,4))) when (d.check_number is not null and d.check_number !='') then concat('Check # ',d.check_number) when d.id > 0 then 'Cash' END as ptype,h.cost amount_paid,0 as amount_duex,h.cost,0 as amount_due , f.id as invoiceid,a.family_account_id,'C' as doctype ");
			$query3->addSelect(" if(a.cancelled_date is null,'',concat(' Voided ', date_format(a.cancelled_date,'%m/%d/%Y') )) as voided,0 with_payment,0 as registration_id,'0' as mykey,e.accounting_invoice_id");
			$query3->addSelect(" a.notes as journal_notes");
			$query3->addSelect("1 as show_on_family_view");
			$query3->addFrom( AccountingJournal::_tablename. " a ");
			$query3->addJoin(" left join ". AccountingPayment::_tablename ." b on a.id=b.journal_id ");
			$query3->addJoin(" left join ". AccountingPaymentCard::_tablename." c on b.id=c.accounting_payment_id");
			$query3->addJoin(" left join ". AccountingPaymentCash::_tablename." d on b.id=d.accounting_payment_id");
			$query3->addJoin(" left join ". AccountingPaymentInvoice::_tablename." e on e.accounting_payment_id=b.id");
			$query3->addJoin(" left join ". AccountingInvoice::_tablename. " f on f.id=e.accounting_invoice_id");
			$query3->addJoin(" left join ". AccountingJournalItem::_tablename ." g on g.journal_id=f.journal_id and g.accounting_type=2");
			$query3->addJoin(" left join ". AccountingJournalItem::_tablename ." h on h.journal_id=b.journal_id and h.accounting_type=2");
			$query3->addJoin(" left join ". PayerCard::_tablename. " i on i.id=c.payer_card_id");
			$query3->addJoin(" left join ". PayerCardType::_tablename ." j on i.card_type_id=j.id ");
			$query3->addJoin(" left join ". AccountingJournalRegistration::_tablename ." x on x.accounting_journal_id=f.journal_id");
			$query3->addJoin(" left join ". Registration::_tablename ." y on y.id=x.registration_id ");
			$query3->addWhere(" a.transaction_type in('".CodesUtility::TRANS_TYPE_PAYMENT."','". CodesUtility::TRANS_TYPE_CREDIT_ON_ACCOUNT."') and a.posted=1 and a.family_account_id = ".$user_family_account_id ." and h.account_code = ". $accountCodesArray[CodesUtility::ACCOUNT_CODE_COA]);
			$query3->addHaving(' invoice_total IS NOT NULL ');
			if(!empty($user_date_from)){
				$query3->addWhere("  date_format(a.transaction_date,'%Y-%m-%d') between '". $user_date_from ."' and '". $user_date_to ."'");
			}
			$query3->addGroupby(" a.id ");
				
			
			#-- refund 
			$query4 = new QueryCreator();
			$query4->addSelect(" all b.id as line_id,transaction_date as tdate,concat('Refund on Inv# ',invoice_number,' ',' Payer ',concat(f.firstname,' ',f.lastname)) as description,if(k.account_code = ".$accountCodesArray[CodesUtility::ACCOUNT_CODE_COA].",'Credit','Refund') as ttype,0 as invoice_total,concat('Refund to ',if(k.account_code = ".$accountCodesArray[CodesUtility::ACCOUNT_CODE_COA].", 'account ' , CASE WHEN h.accounting_payment_id > 0 then ' Cash/Check ' WHEN g.accounting_payment_id > 0 then concat(j.value,' ',concat(repeat('X',4),'-',right(i.card_number,4))) END)) as ptype,-sum(ifnull(c.amount,c1.amount)) as amount_paid,0 as amount_duex,0 as credits,0 as amount_due,a.accounting_payment_id as invoiceid,b.family_account_id,'R' as doctype");
			$query4->addSelect(" if(b.cancelled_date is null,'',concat(' Voided ', date_format(b.cancelled_date,'%m/%d/%Y') )) as voided,if(g.id>0,1,0) as with_payment,d.id as registration_id,'0' as  mykey,c.accounting_invoice_id");
			$query4->addSelect(" b.notes as journal_notes");
			
			if($forPublic == false)
				$query4->addSelect(" 1 as show_on_family_view");
			else
				$query4->addSelect(" LOCATE('public_invoice', ifnull(ajnv.show_notes_on, '0')) > 0 as show_on_family_view");
			$query4->addFrom(AccountingRefund::_tablename. " a ");
			$query4->addJoin(" left join ". AccountingJournal::_tablename ." b on a.journal_id=b.id ");
			$query4->addJoin(" LEFT JOIN ". AccountingJournalNotesView::_tablename. " ajnv ON  ajnv.accounting_journal_id = b.id");
			$query4->addJoin(" left join ". AccountingJournalRegistration::_tablename ." c on c.accounting_journal_id=b.id");
			$query4->addJoin(" left join ". AccountingJournalItemCharge::_tablename . " c1 on c1.accounting_journal_id=b.id ");
			$query4->addJoin(" left join ". Registration::_tablename ." d on d.id=c.registration_id ");
			$query4->addJoin(" left join ". BaseCatalog::_tablename ." e on d.catalog_session_id=e.id ");
			$query4->addJoin(" left join ". Payer::_tablename ." f on f.id=a.payer_id");
			$query4->addJoin(" left join ". AccountingPaymentCard::_tablename ." g on g.accounting_payment_id = a.accounting_payment_id");
			$query4->addJoin(" left join ". AccountingPaymentCash::_tablename ." h on h.accounting_payment_id = a.accounting_payment_id");
			$query4->addJoin(" left join ". PayerCard::_tablename ." i on i.id=g.payer_card_id ");
			$query4->addJoin(" left join ". PayerCardType::_tablename ." j on i.card_type_id=j.id");
			$query4->addJoin(" left join ". AccountingJournalItem::_tablename ." k on k.journal_id=a.journal_id and accounting_type = ". CodesUtility::ACCOUNTING_TYPE_CREDIT );
			$query4->addHaving(' invoice_total IS NOT NULL ');
		//	$query4->addJoin(" left join ". AccountingPaymentInvoice::_tablename . " api on api.accounting_payment_id=a.accounting_payment_id and api.accounting_invoice_id = c.accounting_invoice_id" );
			$query4->addJoin(" left join ". AccountingInvoice::_tablename . " ai on ai.id=c.accounting_invoice_id");
			$query4->addWhere(" b.family_account_id = ". $user_family_account_id );
			if(!empty($user_date_from)){
				$query4->addWhere(" date_format(b.transaction_date,'%Y-%m-%d') between '". $user_date_from ."' and '". $user_date_to ."'");
			}
			$query4->addGroupby('a.id');	
			
			#query co-sponsor invoice later
			$query5 = new QueryCreator();
			$query5->addSelect(" all a.id as line_id,a.transaction_date as tdate,concat(catalog.name,'<br> Not invoiced yet <br>',concat(payer.lastname,',',if(payer.lastname = payer.firstname,'',payer.firstname))) as description,'Debit' as ttype,sum(a.amount) as invoice_total,'' as ptype,0 as amount_paid, 0 as amount_duex,0 as credits,b.cost as amount_due,0 as invoiceid,b.family_account_id,'S' as doctype,0 as with_payment,b.id as registration_id");
			$query5->addSelect(" '' as voided,'0' as mykey,0 as accounting_invoice_id");
			$query5->addSelect(" '' as journal_notes");
			$query5->addSelect("1 as show_on_family_view");
			$query5->addFrom(RegistrationCosponsor::_tablename ." a ");
			$query5->addJoin(" left join ". Registration::_tablename . " b  on a.registration_id = b.id ");
			$query5->addJoin(" left join ". RegistrationChild::_tablename ." child on child.registration_id=b.id ");
			$query5->addJoin(" left join ". BaseCatalog::_tablename ." catalog on catalog.id=b.catalog_session_id ");
			$query5->addJoin(" left join ". Payer::_tablename . " payer on payer.id=a.cosponsor_id");
			$query5->addWhere(" accounting_invoice_id = 0 and b.family_account_id = ". $user_family_account_id);
			$query5->addHaving(' invoice_total IS NOT NULL ');
			$query5->addGroupby(" a.cosponsor_id,b.id");
		
		
			#misc charge
			$query6 = new QueryCreator();
			/**
				$query6->addSelect(" a.id as line_id,a.transaction_date as tdate,Concat('Miscellaneous Charge - ',l.name) as description");
				$query6->addSelect(" CASE when transaction_type=1 then  'Debit'  when transaction_type=2 then 'Invoice' when transaction_type=3 then 'Refunds' when transaction_type=4 then 'Credit' END as ttype ,CASE when transaction_type=2 then  sum(d.amount) END as invoice_total,CASE when c.invoice_number is not null then concat('Inv ',invoice_number)  END  as ptype,0 as amount_paid,sum(d.amount) as amount_due,0 as credits,0 , c.id as invoiceid,a.family_account_id,'I' as doctype,if(a.cancelled_date is null,'',concat(' Voided ', date_format(a.cancelled_date,'%m/%d/%Y') )) as voided,0 as with_payment,e.id as registration_id,'0' as mykey,c.id as accounting_invoice_id "); 
				
				the above lines were replaced because: 
					
				1) when there are two or more charges 
					for a single misc charge invoice, 
					it presents a problem on naming the invoice
					especially if the misc charges are of 
					different kinds. (i.e. an invoice named
					"Miscellaneous Charge - Late Fee" does not 
					guarantee that all of the charges in this 
					invoice consist of Late Fees only.)
				
				2) when there are two or more charges in a 
					misc charge invoice, the invoice total and 
					amount due values are incorrect.
			**/
			$query6->addSelect(
				"a.id as line_id,".
				"a.transaction_date as tdate,".
				"CONCAT".
				"(".
					"'Miscellaneous Charge', ".
					"IF".
					"(".
						"(SELECT count(inner_ajic.id)". 
							"FROM accounting_journal_items_charges as inner_ajic ".
							"WHERE inner_ajic.accounting_journal_id = a.id".
						") = 1, CONCAT(' - ', l.name), ".
						"'s')".
				") as description, ".
				"'Invoice' as ttype ,".
				"(SELECT sum(inner_ajic.amount) ".
					"FROM accounting_journal_items_charges as inner_ajic ".
					"WHERE inner_ajic.accounting_journal_id = a.id".
				")  as invoice_total,".
				"CASE when c.invoice_number is not null ". 
							"then concat('Inv ',invoice_number) ".
					"END as ptype, ".
				"0 as amount_paid, ".
				"(SELECT sum(inner_ajic.amount) ".
					"FROM accounting_journal_items_charges as inner_ajic ".
					"WHERE inner_ajic.accounting_journal_id = a.id".
				") as amount_due, ".
				"0 as credits, ".
				"0 , ".
				"c.id as invoiceid, ".
				"a.family_account_id, ".
				"'I' as doctype, ".
				"if".
				"(".
					"a.cancelled_date is null,".
					"'',".
					"concat".
					"(' Voided ', ".
						"date_format(a.cancelled_date,'%m/%d/%Y')".
					")".
				") as voided,".
				"0 as with_payment,".
				"e.id as registration_id,".
				"'0' as mykey,".
				"c.id as accounting_invoice_id ");
			$query6->addSelect(" a.notes as journal_notes");
			if($forPublic == false)
				$query6->addSelect(" 1 as show_on_family_view");
			else
				$query6->addSelect(" LOCATE('public_invoice', ifnull(ajnv.show_notes_on, '0')) > 0 as show_on_family_view");
			$query6->addFrom( AccountingJournal::_tablename.' a ');
			$query6->addJoin(" LEFT JOIN ". AccountingJournalNotesView::_tablename. " ajnv ON  ajnv.accounting_journal_id = a.id");
			$query6->addJoin('left join '. AccountingInvoice::_tablename. ' c on c.journal_id=a.id ');
			$query6->addJoin('left join '. AccountingJournalItemCharge::_tablename. ' d on d.accounting_journal_id=c.journal_id');
			$query6->addJoin('left join '. Registration::_tablename .' e on e.id=d.registration_id');
			$query6->addJoin('left join '. RegistrationChild::_tablename .' f on f.registration_id=e.id');
			$query6->addJoin('left join '. ChildInfo::_tablename .' g on g.id=f.child_id');
			$query6->addJoin('left join '. FamilyAccountPayer::_tablename .' i on i.payer_id=c.payer_id');
			$query6->addJoin('left join '. Payer::_tablename .' j on j.id=c.payer_id');
			$query6->addJoin('left join '. PayerType::_tablename .' k on j.payer_type_id = k.id ');
			$query6->addJoin('left join '. MiscellaneousCharge::_tablename .' l on l.id=d.charge_id');
			$query6->addWhere(' a.transaction_type=2 and a.posted=1 and a.family_account_id = '. $user_family_account_id ." and d.amount > 0");
			$query6->addHaving(' invoice_total IS NOT NULL ');
			if(!empty($user_date_from)){
				$query6->addWhere("  date_format(a.transaction_date,'%Y-%m-%d') between '". $user_date_from ."' and '". $user_date_to ."'");
			}
			//$query->addWhere(' a.cancelled_date is null ');
			//$query6->addGroupby('d.charge_id');
			$query6->addGroupby('a.id,c.id' );
			//echo $query6->createSQL();
			
			
			
			$query7 = new QueryCreator();
			$query7->addSelect(
				' aprs.id as line_id, '.
				'aprs.last_updated as tdate, '.
				'"Recurring Payment Schedule Set Up" as description,'.
				'"" as ttype, '.
				'0.00 as invoice_total, '.
				'CASE payperiod '.
					'WHEN 1 THEN '.
						'Concat'.
						'("Monthly on " , '.
							'DATE_FORMAT(STR_TO_DATE(payday1,"%d"),"%D")'.
						') '.
					'WHEN 2 THEN '.
						'Concat'.
						'("Bi-Weekly on ", '.
						  'DATE_FORMAT(STR_TO_DATE(payday1,"%d"),"%D"), '.
						  '" & " , '.
						  'DATE_FORMAT(STR_TO_DATE(payday2,"%d"),"%D")'.
						') '.
					'WHEN 3 THEN Concat("Weekly") '.
				'END as ptype, '.
				'0.00 as amount_paid, '.
				'0.00 as amount_due, '.
				'0.00 as credits, '.
				'0, '.
				'0 as invoiceid, '.
				'fap.family_account_id as family_account_id, '.
				'"K" as doctype, '.
				'"" as voided, '.
				'0 as with_payment, '.
				'0 as registration_id, '.
				'0 as mykey, '.
				'0 as accounting_invoice_id, '.
				' "" as journal_notes');
			$query7->addSelect(" 1 as show_on_family_view");
			$query7->addFrom(
				AccountingPaymentRecurringSetup::_tablename . 
				' aprs ');
			$query7->addJoin(
				" LEFT JOIN " . AccountingPaymentRecurring::_tablename . 
				" apr ".
				" ON apr.recurring_setup_id = aprs.id ".
				" AND apr.deleted = 0");
			$query7->addJoin(
				" LEFT JOIN " . PayerCard::_tablename . 
				" pc ON pc.id = aprs.payer_card_id ");
			$query7->addJoin(
				" LEFT JOIN " . FamilyAccountPayer::_tablename . 
				" as fap ".
				" ON fap.payer_id = pc.payer_id ");
			$query7->addWhere(
				" fap.family_account_id  = ". $user_family_account_id);
			$query7->addWhere(" aprs.deleted  = 0 ");
			/**
			$query7->addHaving(' invoice_total IS NOT NULL '); 
				above line was removed because 
				invoice_total is always equal to 0.00
				
				@modified by: Stacey K.D. Cardoso
				@email: stacey.dwebsite.net
				@on: 7-April-2009
			**/
			$query7->addGroupby(' line_id ');
	
	
			$db = DBCon::instance();
			//echo $query6->createSQL(); die();
			//echo '('. $query->createSQL()  .') <br>union all<br> ('. $query2->createSQL() .') <br>union all<br> ('. $query3->createSQL() .') <br>union all<br> ('. $query4->createSQL() .') <br>union all<br> ('. $query5->createSQL() .') <br>union all<br> ('. $query6->createSQL() .') union all ('. $query7->createSQL() .')  order by tdate,line_id'; die();

			$result = $db->executeQuery(
				'('. $query->createSQL()  .') union all '.
				'('. $query2->createSQL() .') union all '.
				'('. $query3->createSQL() .') union all '.
				'('. $query4->createSQL() .') union all '.
				'('. $query5->createSQL() .') union all '.
				'('. $query6->createSQL() .') union all '.
				'('. $query7->createSQL() .') '.
				' order by tdate,line_id');
			
			return $result;
	}
	
	
	public function getListchildren($family_account_id=0,$period_from='',$period_to='',$includeCosponsor=1,$cosponsorinvoice=0,$allmonths=false,$noRecurringPayments=false,$regMonthId=0,$coSponsorId=0)
	{
		
		#query the list of children registered in a caresession
		#without invoice only
		Zend_loader::loadClass("Registration");
		Zend_Loader::loadClass("RegistrationChild");
		Zend_Loader::loadClass("ChildInfos");
		Zend_loader::loadClass("AccountingInvoice");
		Zend_loader::loadClass("AccountingJournalItem");
		Zend_loader::loadClass("AccountingJournalRegistration");
		Zend_loader::loadClass("AccountingJournal");
		Zend_Loader::loadClass("RegistrationLog");
		Zend_loader::loadClass("RegistrationCosponsor");
		Zend_loader::loadClass("Payer");
		Zend_loader::loadClass("PayerCard");
		Zend_loader::loadClass("BaseCatalog");
		Zend_loader::loadClass("RegistrationMonth");
		Zend_Loader::loadClass("AccountingPaymentRecurring");
		Zend_Loader::loadClass("FamilyAccountPayer");
		
		
		if ($family_account_id>0){
			$query = new QueryCreator();
			if($cosponsorinvoice){
				$st = "   e.cost - e.discount - e.scholarship - e.price_tier_amount - ifnull((
			select sum(ifnull(amount,0)) from ". AccountingInvoice::_tablename ." x  left join ". AccountingJournalRegistration::_tablename ." x3 on x3.accounting_journal_id = x.journal_id left join ". Registration::_tablename ." x4 on x4.id=x3.registration_id left join ". AccountingJournal::_tablename ." x5 on x5.id=x.journal_id where x4.family_account_id= " . $family_account_id. " and x5.cancelled_date is null and a.id=x3.registration_id and x3.registration_month_id=e.id
			),0) - ifnull((select sum(amount) from ". RegistrationCosponsor::_tablename. " x where x.registration_id = a.id and x.accounting_invoice_id = 0 and x.registration_month_id=e.id and x.id != ". $coSponsorId ." ),0)";
			} else {
				$st = " e.cost - e.discount - e.scholarship - e.price_tier_amount - ifnull((
			select sum(ifnull(amount,0)) from ". AccountingInvoice::_tablename ." x  left join ". AccountingJournalRegistration::_tablename ." x3 on x3.accounting_journal_id = x.journal_id left join ". Registration::_tablename ." x4 on x4.id=x3.registration_id left join ". AccountingJournal::_tablename ." x5 on x5.id=x.journal_id where x4.family_account_id= " . $family_account_id. " and x5.cancelled_date is null and a.id=x3.registration_id and x3.registration_month_id=e.id
			),0)";
			}
			
			$query->addSelect(" concat(c.firstname,' ',c.lastname) as name,a.id as reg_id , c.id as child_id , c.firstname, c.lastname");-
			$query->addSelect( $st ." as cost,e.id as registration_month_id,e.month,e.year");
			
			if(!empty($period_from)){
				$query->addSelect("(select edited_on from " .RegistrationLog::_tablename. " d where d.registration_id = a.id order by edited_on asc limit 1) as reg_date");
			}
			$query->addSelect("'' as sponsor,'0' as cosponsor_id");
			$query->addSelect(" d.name as catalog_name , d.id as catalog_session_id ");
			$query->addFrom( Registration::_tablename ." a");
			$query->addJoin(" left join ". RegistrationChild::_tablename ." b on a.id=b.registration_id");
			$query->addJoin(" left join ". ChildInfo::_tablename ." c on b.child_id=c.id ");
			$query->addJoin(" left join ". BaseCatalog::_tablename ." d on d.id=a.catalog_session_id");
			
			$monthCondition = "";
			if(!$allmonths){
				$monthCondition = " and (e.month = month(current_date) and e.year=year(current_date) or e.month=0) ";
			}
			
			if($coSponsorId){
				Zend_Loader::loadClass("RegistrationCosponsor");
				Zend_Loader::loadClass("RegistrationChild");
				$RegistrationChild = new RegistrationChild();
				
				$RegCosponsor = new RegistrationCosponsor();
				$RegCosponsor->loadWhere("id = ". $coSponsorId);
				
				$RegistrationChild->loadWhere("registration_id = ". $RegCosponsor->registration_id );
				
				$query->addWhere(" b.child_id = ". $RegistrationChild->child_id);
			}
			
			if($regMonthId){
				$query->addWhere(" e.id = ". $regMonthId );
			}
			
			$query->addJoin(" left join ". RegistrationMonth::_tablename . " e on e.registration_id=a.id ". $monthCondition );
			
			if($noRecurringPayments){
				$query->addJoin(" left join ". AccountingPaymentRecurring::_tablename . " apr on apr.registration_month_id = e.id  ");
				$query->addJoin(" left join ". AccountingPaymentRecurringSetup::_tablename . " aprs on aprs.id = apr.recurring_setup_id  ");
				$query->addJoin(" left join ". PayerCard::_tablename . " pc ON aprs.payer_card_id = pc.id  ");
				$query->addJoin(" left join ". FamilyAccountPayer::_tablename . " fap ON fap.payer_id = pc.payer_id AND fap.family_account_id = a.family_account_id  ");
				$query->addWhere(" ( apr.id IS NULL OR aprs.deleted = 1 ) " );
			}			
			
			$query->addWhere(" a.family_account_id = " . $family_account_id );
			$query->addWhere(" a.cancelled=0 and a.deleted = 0 ");	
			if(!$allmonths){
				$query->addWhere(" ((e.month = month(current_date) and e.year=year(current_date)) or e.month=0) ");
			}
			
			if(!empty($period_from)){
				$query->addHaving(" reg_date between '". $period_from ."' and '". $period_to ."' and cost > 0 order by name " );
			} else {
				$query->addWhere(" 1=1 having cost > 0 order by name");
			}
			
			
			if($includeCosponsor){
				$query2 = new QueryCreator();
				$query2->addSelect("concat(c.firstname,' ',c.lastname) as name,a.registration_id as reg_id, c.id as child_id, c.firstname, c.lastname");
				$query2->addSelect("sum(amount) as cost,a.registration_month_id,g.month,g.year");
				if(!empty($period_from)){
					$query2->addSelect("(select edited_on from " .RegistrationLog::_tablename . " d where d.registration_id = a.registration_id order by edited_on asc limit 1) as reg_date");
				}
				$query2->addSelect("concat(' [',f.lastname,',',f.firstname,']') as sponsor,a.cosponsor_id");
				$query2->addSelect(" i.name as catalog_name , i.id as catalog_session_id ");
				$query2->addFrom(RegistrationCosponsor::_tablename ." a ");
				$query2->addJoin(" left join ". RegistrationChild::_tablename ." b on a.registration_id = b.registration_id ");
				$query2->addJoin(" left join ". ChildInfo::_tablename ." c on c.id=b.child_id");
				$query2->addJoin(" left join ". Registration::_tablename ." e on e.id=a.registration_id");
				$query2->addJoin(" left join ". BaseCatalog::_tablename ." i on i.id=e.catalog_session_id");
				$query2->addJoin(" left join ". Payer::_tablename ." f on f.id=a.cosponsor_id");
				$query2->addJoin(" left join ". RegistrationMonth::_tablename ." g on g.id=a.registration_month_id");
				if($noRecurringPayments){
					$query2->addJoin(" left join ". AccountingPaymentRecurring::_tablename . " apr on apr.registration_month_id = g.id ");
					$query2->addJoin(" left join ". AccountingPaymentRecurringSetup::_tablename . " aprs on aprs.id = apr.recurring_setup_id  ");
					$query2->addJoin(" left join ". PayerCard::_tablename . " pc ON aprs.payer_card_id = pc.id  ");
					$query2->addJoin(" left join ". FamilyAccountPayer::_tablename . " fap ON fap.payer_id = pc.payer_id AND fap.family_account_id = a.family_account_id  ");
					$query2->addWhere(" ( apr.id IS NULL OR aprs.deleted = 1 ) " );
				}			

				$query2->addWhere(" accounting_invoice_id = 0");
				$query2->addWhere(" e.family_account_id = " . $family_account_id );
				$query2->addGroupby("cosponsor_id");
			}
			
			$db = DBCon::instance();
			if($includeCosponsor){
				$query = '( '.$query->createSQL() .' ) union all ('. $query2->createSQL() .' )';
			} else {
				$query = $query->createSQL();
			}
			//echo $query;
			$result = $db->executeQuery($query);
			
			return $result;
		}

	}
	
	public function getListActivityPaid($family_account_id,$registration_id =0){
	
		Zend_loader::loadClass("AccountingPaymentInvoice");
		Zend_loader::loadClass("AccountingPayment");
		Zend_loader::loadClass("AccountingInvoice");
		Zend_loader::loadClass("AccountingJournal");
		Zend_loader::loadClass("AccountingJournalItem");
		Zend_loader::loadClass("AccountingJournalRegistration");
		Zend_loader::loadClass("Registration");
		Zend_loader::loadClass("BaseCatalog");
		Zend_loader::loadClass("AccountingJournalItem");
		Zend_loader::loadClass("PayerCard");
		Zend_Loader::loadClass("PayerCardType");
		Zend_loader::loadClass("AccountingPaymentCash");
		Zend_loader::loadClass("AccountingPaymentCard");
		Zend_loader::loadClass("AccountingRefund");
		Zend_loader::loadClass("AccountingJournalItemCharge");
		Zend_Loader::loadClass("RegistrationChild");
		Zend_Loader::loadClass("ChildInfo");
		
		$query = new QueryCreator();
		
		$query->addSelect("0 as child_id,0 as charge_id,'I' as doctype,concat(g.name,'|','|',concat(ci.lastname,',',ci.firstname),'|','$'");
		$query->addSelect("sum(e.amount) - ifnull((select sum(ajr.amount) from ". AccountingRefund::_tablename ." ar LEFT JOIN ". AccountingJournalRegistration::_tablename ." ajr on ar.journal_id = ajr.accounting_journal_id LEFT JOIN ". AccountingJournal::_tablename ." aj on aj.id = ar.journal_id where aj.cancelled_date is null and e.accounting_invoice_id = ajr.accounting_invoice_id and e.registration_month_id = ajr.registration_month_id and e.registration_id = ajr.registration_id ),0) ");
		$query->addSelect("'|',CASE when i.accounting_payment_id>0 then 'By CC' when j.accounting_payment_id>0 and (j.check_number is not null or j.check_number != '') then 'By Check' When j.accounting_payment_id>0 and (j.check_number is  null or j.check_number = '') THEN 'By Cash'  when (i.accounting_payment_id is null and j.accounting_payment_id is null) then 'Credit on Account' END) as activity,CASE when i.accounting_payment_id>0 then concat(value,' ',concat(repeat('X',length(card_number)-4),'-',right(card_number,4)) ) when j.accounting_payment_id>0 then j.check_number when (i.accounting_payment_id is null and j.accounting_payment_id is null) then 'Credit on Account' END  as payment_info,f.id as reg_id,a.payer_id,e.id as line_payment_id");
		//$query->addSelect("sum(e.amount) - sum(ifnull(if(o.cancelled_date is null,n.amount,0),0)) as balance,a.id as payment_id,e.accounting_invoice_id,e.registration_month_id ");
		$query->addSelect("sum(e.amount) - ifnull((select sum(ajr.amount) from ". AccountingRefund::_tablename ." ar LEFT JOIN ". AccountingJournalRegistration::_tablename ." ajr on ar.journal_id = ajr.accounting_journal_id LEFT JOIN ". AccountingJournal::_tablename ." aj on aj.id = ar.journal_id where aj.cancelled_date is null and e.accounting_invoice_id = ajr.accounting_invoice_id and e.registration_month_id = ajr.registration_month_id and e.registration_id = ajr.registration_id ),0) as balance,a.id as payment_id,e.accounting_invoice_id,e.registration_month_id ");
		
		$query->addFrom( AccountingPayment::_tablename." a ");
		$query->addJoin("left join ". AccountingPaymentInvoice::_tablename." b on a.id=b.accounting_payment_id ");
		$query->addJoin("left join ". AccountingInvoice::_tablename." c on b.accounting_invoice_id = c.id  ");
		$query->addJoin("left join ". AccountingJournal::_tablename ." d on d.id=a.journal_id ");
		$query->addJoin("left join ". AccountingJournalRegistration::_tablename." e on e.accounting_journal_id=d.id and e.accounting_invoice_id=b.accounting_invoice_id ");
		$query->addJoin("left join ". Registration::_tablename ." f on f.id=e.registration_id ");
		$query->addJoin("left join ". BaseCatalog::_tablename ." g on g.id=f.catalog_session_id");
		$query->addJoin("left join ". AccountingJournalItem::_tablename." h on h.journal_id=d.id and accounting_type=1");
		$query->addJoin("left join ". AccountingPaymentCard::_tablename." i on i.accounting_payment_id=a.id");
		$query->addJoin("left join ". AccountingPaymentCash::_tablename." j on j.accounting_payment_id=a.id");
		$query->addJoin("left join ". PayerCard::_tablename. " k on k.id=i.payer_card_id");
		$query->addJoin("left join ". PayerCardType::_tablename." l on l.id=k.card_type_id");
		//$query->addJoin("left join ". AccountingRefund::_tablename ." m on m.accounting_payment_id=a.id ");
		//$query->addJoin("left join ". AccountingJournalRegistration::_tablename ." n on n.accounting_journal_id=m.journal_id and n.registration_id = e.registration_id and n.accounting_invoice_id = e.accounting_invoice_id and n.registration_month_id=e.registration_month_id");
		//$query->addJoin("left join ". AccountingJournal::_tablename ." o on o.id=m.journal_id");
		$query->addJoin("LEFT JOIN ". RegistrationChild::_tablename ." rc on rc.registration_id = f.id");
		$query->addJoin("LEFT JOIN ". ChildInfo::_tablename . " ci on ci.id=rc.child_id" );
		$query->addWhere(" d.family_account_id=".$family_account_id." and d.cancelled_date is null and e.fully_refunded=0");
		
		if($registration_id != 0){
			$query->addWhere(" e.registration_id in (" . $registration_id .")");
		}
		
		$query->addGroupby(" a.id,c.id  having balance >0");
		//echo $query->createSQL();die();
		$query2 = new QueryCreator();
		$query2->addSelect("e.child_id,e.charge_id,'C' as doctype,concat(g.name,'|','|',concat(ci.lastname,',',ci.firstname),'|','$',sum(e.amount) - sum(ifnull(if(o.cancelled_date is null,n.amount,0),0)),'|',CASE when i.accounting_payment_id>0 then 'by_cards' when j.accounting_payment_id>0 then 'by_cash_checks'  when (i.accounting_payment_id is null and j.accounting_payment_id is null) then 'Credit on Account' END) as activity,CASE when i.accounting_payment_id>0 then concat(value,' ',concat(repeat('X',length(card_number)-4),'-',right(card_number,4)) ) when j.accounting_payment_id>0 then j.check_number when (i.accounting_payment_id is null and j.accounting_payment_id is null) then 'Credit on Account' END  as payment_info,f.id as reg_id,a.payer_id,e.id as line_payment_id,sum(e.amount) - sum(ifnull(if(o.cancelled_date is null,n.amount,0),0)) as balance,a.id as payment_id,e.accounting_invoice_id,0 ");
		$query2->addFrom( AccountingPayment::_tablename." a ");
		$query2->addJoin("left join ". AccountingPaymentInvoice::_tablename." b on a.id=b.accounting_payment_id ");
		$query2->addJoin("left join ". AccountingInvoice::_tablename." c on b.accounting_invoice_id = c.id  ");
		$query2->addJoin("left join ". AccountingJournal::_tablename ." d on d.id=a.journal_id ");
		$query2->addJoin("left join ". AccountingJournalItemCharge::_tablename." e on e.accounting_journal_id=d.id and e.accounting_invoice_id=b.accounting_invoice_id ");
		$query2->addJoin("left join ". Registration::_tablename ." f on f.id=e.registration_id ");
		$query2->addJoin("left join ". BaseCatalog::_tablename ." g on g.id=f.catalog_session_id");
		//$query2->addJoin("left join ". AccountingJournalItem::_tablename." h on h.journal_id=d.id and accounting_type=1");
		$query2->addJoin("left join ". AccountingPaymentCard::_tablename." i on i.accounting_payment_id=a.id");
		$query2->addJoin("left join ". AccountingPaymentCash::_tablename." j on j.accounting_payment_id=a.id");
		$query2->addJoin("left join ". PayerCard::_tablename. " k on k.id=i.payer_card_id");
		$query2->addJoin("left join ". PayerCardType::_tablename." l on l.id=k.card_type_id");
		$query2->addJoin("left join ". AccountingRefund::_tablename ." m on m.accounting_payment_id=a.id ");
		$query2->addJoin("left join ". AccountingJournalItemCharge::_tablename ." n on n.accounting_journal_id=m.journal_id and n.registration_id = e.registration_id and n.charge_id=e.charge_id and n.child_id=e.child_id ");
		$query2->addJoin("left join ". AccountingJournal::_tablename ." o on o.id=m.journal_id");
		$query2->addWhere(" d.family_account_id=".$family_account_id." and d.cancelled_date is null and e.fully_refunded=0");
		$query2->addJoin("LEFT JOIN ". RegistrationChild::_tablename ." rc on rc.registration_id = f.id");
		$query2->addJoin("LEFT JOIN ". ChildInfo::_tablename . " ci on ci.id=rc.child_id" );
		if($registration_id != 0){
			$query->addWhere(" e.registration_id in (" . $registration_id .")");
		}
		
		$query2->addGroupby(" a.id,c.id  having balance >0");
		
		$db = DBCon::instance();
		$result = $db->executeQuery('('. $query->createSQL() .') union ('. $query2->createSQL() .')' );
		//echo '('. $query->createSQL() .') union ('. $query2->createSQL() .')';
		return $result;
		
	}
	
	public function getListInvoiceBalance(
		$user_family_account_id=0,
		$program = array(),
		$category = array(),
		$caresession = array(),
		$location = array(),
		$daterange = '',
		$payerid=0,
		$param=array(
			'cosponsor'=>'all',
			'parent' => 'all',
			'voidedinvoice' => 'false',
			'withbalance'=> 'true',
			'groupby' => 'registration'),
		$showall='0', 
		$includePayerEmail = false){
	
		//print_r($param);
	
			Zend_Loader::loadClass("Registration");
			Zend_Loader::loadClass("RegistrationChild");
			Zend_Loader::loadClass("ChildInfos");
			Zend_Loader::loadClass("RegistrationLog");
			Zend_Loader::loadClass("CatalogSchedule");
			Zend_Loader::loadClass("SeasonPeriod");
			Zend_Loader::loadClass("BaseCatalog");
			
			Zend_loader::loadClass("AccountingJournal");
			Zend_loader::loadClass("AccountingInvoice");
			Zend_loader::loadClass("AccountingJournalRegistration");
			Zend_loader::loadClass("AccountingJournalItemCharge");
			Zend_loader::loadClass("AccountingPaymentInvoice");
			Zend_loader::loadClass("AccountingPayment");
			Zend_loader::loadClass("AccountingJournalItem");
			Zend_loader::loadClass("CodesUtility");
			Zend_Loader::loadClass("AccountingRefund");
			Zend_Loader::loadClass("FamilyAccountPayer");
			Zend_Loader::loadClass("MiscellaneousCharge");
			Zend_loader::loadClass("SchedulingLocation");
			Zend_loader::loadClass("Payer");
			Zend_loader::loadClass("PayerType");
			Zend_Loader::loadClass("PayerContact");
			Zend_Loader::loadClass("RegistrationMonth");
			Zend_Loader::loadClass("CatalogCareSetting");
			
			Zend_Loader::loadClass("AccountingNsfCheck");
			Zend_Loader::loadClass("AccountingPaymentCash");
			
			$groupby  = $param['groupby'];
			# list of all invoice + additional charges - less payment
	
			$query = new QueryCreator();
			$query->addSelect(" 'I' as doctype,b.id as invoice_id,d.id as registration_id ,concat(f.firstname, concat(' ', if(LENGTH(f.middlename) < 1, '', concat(left(f.middlename, 1), '.')), ' '),f.lastname) as name, f.firstname ,f.middlename, f.lastname,  g.name as session_name,sum(c.amount) as cost");
			
			$query->addSelect("ifnull((select date_format(edited_on,'%m/%d/%Y') from ".RegistrationLog::_tablename." x where x.registration_id = c.registration_id order by edited_on limit 1 ),date_format(a.transaction_date,'%m/%d/%Y') ) as reg_date_logs");
			
			
			$query->addSelect("  ifnull((select sum(b2.amount) from ". AccountingPayment::_tablename ." a2 left join ". AccountingJournalRegistration::_tablename ." b2 on a2.journal_id=b2.accounting_journal_id left join ". AccountingJournal::_tablename ." c2 on c2.id=a2.journal_id  left join  ". AccountingPaymentInvoice::_tablename ." d2 on d2.accounting_payment_id=a2.id and d2.accounting_invoice_id=b2.accounting_invoice_id where c2.cancelled_date is null and b2.registration_id=c.registration_id  and b2.accounting_invoice_id = b.id and b2.registration_month_id=c.registration_month_id and c2.cancelled_date is null ),0)
			
			- ifnull(( select sum(c1.amount) from ". AccountingRefund::_tablename ." a1 left join ". AccountingJournal::_tablename ." b1 on a1.journal_id=b1.id left join ". AccountingJournalRegistration::_tablename ." c1 on c1.accounting_journal_id=b1.id where c1.registration_id=d.id and b1.cancelled_date is null and c1.accounting_invoice_id = b.id  and c1.registration_month_id=c.registration_month_id and b1.cancelled_date is null ),0)
			 as amount_paid");
			
			$query->addSelect("sum(c.amount)  -  ifnull((select sum(ajr.amount) from ". AccountingPayment::_tablename ." a2 left join ". AccountingPaymentInvoice::_tablename." b2 on a2.id=b2.accounting_payment_id left join ". AccountingJournalRegistration::_tablename ." ajr on ajr.accounting_invoice_id=b2.accounting_invoice_id and ajr.accounting_journal_id=a2.journal_id left join ". AccountingJournal::_tablename ." c2 on c2.id=a2.journal_id  where c2.cancelled_date is null and ajr.registration_id=c.registration_id and ajr.accounting_invoice_id=b.id and ajr.registration_month_id = c.registration_month_id and c2.cancelled_date is null ),0)
			 
			+ ifnull(( select sum(c2.amount) from ". AccountingRefund::_tablename ." a2 left join ". AccountingJournal::_tablename ." b2 on a2.journal_id=b2.id left join ". AccountingJournalRegistration::_tablename ." c2 on c2.accounting_journal_id=b2.id where c2.registration_id=d.id and b2.cancelled_date is null and c2.accounting_invoice_id = b.id and c2.registration_month_id=c.registration_month_id and b2.cancelled_date is null ),0) 
			as amount_due ");
			
			$query->addSelect(" 0 as additional_cost,b.invoice_number as invoice_no	 ");
			$query->addSelect("i.firstname as payerfirstname,i.lastname as payerlastname,b.payer_id,datediff(current_date,b.date_due) as age,b.id as invoice_id,a.cancelled_date,c.registration_month_id");
			$query->addSelect("(select id from ". AccountingJournalItemCharge::_tablename ." ajic where ajic.accounting_journal_id = a.id limit 1) as charge,b.payer_id,if(k.month > 0,concat(' - ',monthname(concat(k.year,'-',k.month,'-01')),' ',k.year),' - Reg. Fee ') as invoice_month");
			$query->addSelect(" allow_partial_payments ");
			if($includePayerEmail == true)
				$query->addSelect("z.value as payeremail");
			#invoice as the base
			$query->addFrom(AccountingJournal::_tablename." a ");
			$query->addJoin(" left join ". AccountingInvoice::_tablename ." b on a.id = b.journal_id");
			$query->addJoin(" left join ". AccountingJournalRegistration::_tablename ." c on c.accounting_journal_id=b.journal_id and b.id=c.accounting_invoice_id");
			$query->addJoin(" left join ". Registration::_tablename ." d on d.id=c.registration_id");
			$query->addJoin(" left join ". RegistrationChild::_tablename ." e on e.registration_id=d.id");
			$query->addJoin(" left join ". ChildInfo::_tablename ." f on f.id=e.child_id");
			$query->addJoin(" left join ". BaseCatalog::_tablename ." g on g.id=d.catalog_session_id ");
			if(count($location) > 1){
				$query->addJoin(" left join ". SchedulingLocation::_tablename . " h on h.catalog_session_id = d.catalog_session_id ");
			}
			$query->addJoin(" left join ". Payer::_tablename ." i on i.id=b.payer_id");
			$query->addJoin(" left join ". PayerType::_tablename ." j on j.id=i.payer_type_id");
			if($includePayerEmail == true)
				$query->addJoin(" LEFT JOIN ". PayerContact::_tablename ." z on z.payer_id = i.id AND z.contact_type_id = 11");
			$query->addJoin(" left join ". RegistrationMonth::_tablename . " k on c.registration_month_id=k.id");
			$query->addJoin(" left join ". CatalogCareSetting::_tablename . " l on l.catalog_session_id = d.catalog_session_id ");
			$query->addWhere(" b.id > 0 and c.registration_id  > 0");
			
			if($user_family_account_id) {	
				if(is_Array($user_family_account_id)){
					$query->addWhere("   a.transaction_type = 2 and c.amount > 0 and a.family_account_id in (". implode($user_family_account_id,',') .") and d.id > 0");
				} else {
					$query->addWhere("  a.transaction_type = 2 and c.amount > 0 and a.family_account_id = ". $user_family_account_id ." and d.id > 0");
				}
			}
			
			
			
			if(count($program) > 1){
				$query->addWhere(" g.session_program_id  in (". implode($program,',') .")");
			}
			
			if(count($category) > 1){
				$query->addWhere(" g.session_category_id  in (". implode($category,',') .")");
			}
			
			if(count($caresession) > 1){
				$query->addWhere(" g.id  in (". implode($caresession,',') .")");
			}
			
			if(count($location) > 1){
				$query->addWhere(" h.location_id in (". implode($location,',') .")");
			}
			
			if($daterange != ''){
				if(is_array($daterange)){
					$query->addWhere(" date_format(a.transaction_date,'%Y-%m-%d') between '". $daterange[0] ."' and '". $daterange[1] ."'" );
				} else {
					$query->addWhere(" date_format(a.transaction_date,'%Y-%m-%d') <= '". $daterange ."'" );
				}
			}
			
			if($payerid){ 
				if(is_array($payerid)){
					$query->addWhere(" b.payer_id in (". implode($payerid,',') .")");
				}else{
					$query->addWhere("b.payer_id = ". $payerid);
				}
			}
			
			
			
			if($param['cosponsor'] == '') {
				$query->addWhereOR("j.code != '". CodesUtility::PAYER_TYPE_SPONSOR ."'");
			} else {
			
				if($param['cosponsor'] == 'true' && $param['parent'] == 'true') {
					$query->addWhere("(j.code = '". CodesUtility::PAYER_TYPE_SPONSOR ."' or j.code = '". CodesUtility::PAYER_TYPE_FAMILY ."')");
				}
			}
			
			if($param['parent'] == ''){
				$query->addWhereOR("j.code != '". CodesUtility::PAYER_TYPE_FAMILY ."'");
			} else {
				
				if($param['cosponsor'] == 'true' && $param['parent'] == 'true') {
					$query->addWhere("(j.code = '". CodesUtility::PAYER_TYPE_SPONSOR ."' or j.code = '". CodesUtility::PAYER_TYPE_FAMILY ."')");
				}
			}

			if($param['voidedinvoice'] == 'false' ){
				$query->addWhere(" a.cancelled_date is null ");
			}
			
			switch ($groupby) {
					case "registration":
						$query->addGroupBy(" b.journal_id,c.registration_id,c.registration_month_id   ");
						break;
					case "invoice":
						$query->addGroupBy(" b.id,c.registration_month_id");
						break;
					default:
						$query->addGroupBy(" b.journal_id,c.registration_month_id ");
						break;
			}
			
			if($param['withbalance']){
				if(!$showall) {
					$query->addHaving(" amount_due > 0" );
				}
			} else {
				
				$query->addHaving(" cost > 0" );
				
			} 
			
			
			$query->addHaving(" charge is null ");
			//echo $query->createSQL();
			
			
			
			//echo  $query->createSQL();
			#miscellaneous charge
			$query2 = new QueryCreator();
			$query2->addSelect(" all 'M' as doctype,b.id as invoice_id,c.id as registration_id,concat(e.firstname, ' ',left(e.middlename,1),'. ',e.lastname) as name , e.firstname ,e.middlename, e.lastname  ");
			$query2->addSelect("concat('Miscelleneous Charge - ',f.name) as session_name,sum(c.amount) as cost,date_format(a.transaction_date,'%m/%d/%Y') reg_date_logs");
			$query2->addSelect(" ifnull((select sum(subb.amount) from ". AccountingPayment::_tablename  ." suba left join ". AccountingJournalItemCharge::_tablename ." subb on suba.journal_id=subb.accounting_journal_id left join ". AccountingJournal::_tablename ." subc on subc.id=suba.journal_id left join ". AccountingPaymentInvoice::_tablename ." subd on subd.accounting_payment_id=suba.id where subc.cancelled_date is null and subb.charge_id=c.charge_id and subb.child_id=c.child_id and b.id=subd.accounting_invoice_id ) - ifnull((select sum(amount) from ". AccountingRefund::_tablename ." as ar left join ". AccountingJournalItemCharge::_tablename . " ajir on ar.journal_id = ajir.accounting_journal_id left join ". AccountingJournal::_tablename ." aj on aj.id=ar.journal_id where aj.cancelled_date is null and c.accounting_invoice_id=ajir.accounting_invoice_id and c.registration_id=ajir.registration_id and ajir.charge_id=c.charge_id and ajir.child_id=c.child_id ),0),0) as amount_paid");
			$query2->addSelect(" sum(c.amount) - ifnull((select sum(subb.amount) from ". AccountingPayment::_tablename  ." suba left join ". AccountingJournalItemCharge::_tablename ." subb on suba.journal_id=subb.accounting_journal_id left join ". AccountingJournal::_tablename  ." subc on subc.id=suba.journal_id left join ". AccountingPaymentInvoice::_tablename ." subd on subd.accounting_payment_id=suba.id where subc.cancelled_date is null and subb.charge_id=c.charge_id and subb.child_id=c.child_id and b.id=subd.accounting_invoice_id ),0) + ifnull((select sum(amount) from ". AccountingRefund::_tablename ." as ar left join ". AccountingJournalItemCharge::_tablename . " ajir on ar.journal_id = ajir.accounting_journal_id left join ". AccountingJournal::_tablename ." aj on aj.id=ar.journal_id where aj.cancelled_date is null and c.accounting_invoice_id=ajir.accounting_invoice_id and c.registration_id=ajir.registration_id and ajir.charge_id=c.charge_id and ajir.child_id=c.child_id ),0) as amount_due,0 as additional_cost,b.invoice_number as invoice_no");
			$query2->addSelect("x.firstname as payerfirstname,x.lastname as payerlastname,b.payer_id,datediff(current_date,b.date_due) as age,b.id  as invoice_id,a.cancelled_date,0 as registration_month_id,1 as charge,b.payer_id,'' as invoice_month,0 as allow_partial_payments");
			if($includePayerEmail == true)
				$query2->addSelect("z.value as payeremail");
				
			$query2->addFrom(AccountingJournal::_tablename ." a ");
			$query2->addJoin(" left join ". AccountingInvoice::_tablename . " b on a.id=b.journal_id ");
			$query2->addJoin(" left join ". AccountingJournalItemCharge::_tablename . " c on c.accounting_journal_id=b.journal_id ");
			$query2->addJoin(" left join ". ChildInfo::_tablename ." e on e.id =c.child_id ");
			$query2->addJoin(" left join ". MiscellaneousCharge::_tablename ." f on f.id = c.charge_id");
			$query2->addJoin(" left join ". Payer::_tablename ." x on x.id=b.payer_id");
			$query2->addJoin(" left join ". PayerType::_tablename ." j on j.id=x.payer_type_id");
			if($includePayerEmail == true)
				$query2->addJoin(" LEFT JOIN ". PayerContact::_tablename ." z on z.payer_id = x.id AND z.contact_type_id = 11");

			$query2->addWhere(" b.id > 0 and c.charge_id > 0");
			
			if($param['voidedinvoice'] == 'false' ){
				$query2->addWhere(" a.cancelled_date is null ");
			}
			
			if($param['cosponsor'] == '') {
				$query2->addWhereOR("j.code != '". CodesUtility::PAYER_TYPE_SPONSOR ."'");
			} else {
			
				if($param['cosponsor'] == 'true' && $param['parent'] == 'true') {
					$query2->addWhere("(j.code = '". CodesUtility::PAYER_TYPE_SPONSOR ."' or j.code = '". CodesUtility::PAYER_TYPE_FAMILY ."')");
				}
			}
			
			if($param['parent'] == ''){
				$query2->addWhereOR("j.code != '". CodesUtility::PAYER_TYPE_FAMILY ."'");
			} else {
				
				if($param['cosponsor'] == 'true' && $param['parent'] == 'true') {
					$query2->addWhere("(j.code = '". CodesUtility::PAYER_TYPE_SPONSOR ."' or j.code = '". CodesUtility::PAYER_TYPE_FAMILY ."')");
				}
			}
			
			if($daterange != ''){
				if(is_array($daterange)){
					$query2->addWhere(" date_format(a.transaction_date,'%Y-%m-%d') between '". $daterange[0] ."' and '". $daterange[1] ."'" );
				} else {
					$query2->addWhere(" date_format(a.transaction_date,'%Y-%m-%d') <= '". $daterange ."'" );
				}
			}
			
			if($payerid){
				if(is_array($payerid)){
					$query2->addWhere(" b.payer_id in (". implode($payerid,',') .")");
				}else{
					$query2->addWhere("b.payer_id = ". $payerid);
				}
			}
			
			if($user_family_account_id) {
				if(is_Array($user_family_account_id)){
					$query2->addWhere(" a.family_account_id in ( ". implode($user_family_account_id,',') ." ) and a.transaction_type = 2 and c.id > 0 ");
				} else {
					$query2->addWhere(" a.family_account_id = ". $user_family_account_id ." and a.transaction_type = 2 and c.id > 0 ");
				}
			}
			
			if($param['withbalance']){
				switch ($groupby) {
					case "invoice":
						$query2->addGroupBy(" b.id");
						break;
					default:
						$query2->addGroupBy(" charge_id  ");
						break;
				}
				
			} else {
				switch ($groupby) {
					case "invoice":
						$query2->addGroupBy(" b.id");
						break;
					default:
						$query2->addGroupBy(" charge_id  having cost > 0 ");
						break;
				}
				
				
			}
			
			$query2->addHaving(" amount_due > 0"); 
			// echo $query->createSQL();
			// echo "<br><br>";
			// echo $query2->createSQL();
			$db = DBCon::instance();
			$result = $db->executeQuery(
				'('. $query->createSQL() .') union all '.
				'('. $query2->createSQL() .') '.
				'order by 2,21');
			return $result;
	}
	
	public function getListOfRegistrationForPayment($registrationids){
		#registrationids variable is an array 
		
		Zend_Loader::loadClass("BaseCatalog");
		Zend_Loader::loadClass("Registration");
		Zend_Loader::loadClass("RegistrationChild");
		Zend_Loader::loadClass("ChildInfos");
		Zend_Loader::loadClass("RegistrationLog");
		Zend_Loader::loadClass("RegistrationMonth");
		Zend_Loader::loadClass("User");
		Zend_Loader::loadClass("Administrator");
		Zend_Loader::loadClass("RegistrationDay");
	
		Zend_Loader::loadClass("CatalogSchedule");
		Zend_Loader::loadClass("SeasonPeriod");
		Zend_loader::loadClass("AccountingJournalRegistration");
		Zend_loader::loadClass("AccountingJournalItemCharge");
		Zend_loader::loadClass('MiscellaneousCharges');
		Zend_loader::loadClass("AccountingJournal");
		Zend_loader::loadClass("AccountingInvoice");
		
		$strregid = '0';
		$strchargeid = '0';
		$invoiceid = '0';
		$regmonthid = '0';
		//print_r($registrationids);
		//die();
		if($registrationids){
			foreach ($registrationids as $key => $value){
				if($value > 0){
					$invoiceid_regid = explode('_',$key);
					
					if($invoiceid_regid[0] == 'I') {
						
						$this_regid = explode(';',$invoiceid_regid[2]);
						$regid = $this_regid[0];
						if(empty($strregid)){
							$strregid = $regid;
							$invoiceid = $invoiceid_regid[1];
							$regmonthid = $this_regid[1];
						} else {
							$strregid .= ','.$regid;
							$invoiceid .= ','. $invoiceid_regid[1];
							$regmonthid .= ','. $this_regid[1];
						}
					} else {
						$this_regid = explode(';',$invoiceid_regid[2]);
						$regid = $this_regid[0]; 
						if(empty($strchargeid)){
							$strchargeid = $regid;
							//$invoiceid = $invoiceid_regid[1];
						} else {
							$strchargeid .= ','.$regid;
							//$invoiceid .= ','. $invoiceid_regid[1];
						}
					}
				}
			}
		}
	
	
		
			$query = new QueryCreator();
			$query->addSelect(" 'I' as doctype,b.child_id,a.id as reg_id,b.id as reg_line_child_id");
			$query->addSelect("(select concat(ifnull(date_format(edited_on,'%m/%d/%Y'),''),',',ifnull(date_format(edited_on,'%h:%i %p'),''),',',if(admin_registration=1,'Admin Side','Public Side'),',',concat(ifnull(admin.firstname,''),' ',ifnull(admin.lastname,''))) from " .RegistrationLog::_tablename. " d left join " .User::_tablename. " user on d.edited_by_id = user.id left join " . Administrator::_tablename. " admin on admin.user_id=user.id where d.registration_id = a.id order by edited_on asc limit 1) as reg_date_logs");
			
			$query->addSelect(" min(day) as start_day,max(day) as end_day,e.id_number as class_number,e.session_id ");
			$query->addSelect(" e.name as session_name,a.catalog_session_id,concat(firstname, ' ',left(middlename,1),'. ',lastname) as name");
			//summation of session start_time and end_time
			//$query->addSelect("time_format(sec_to_time(sum(time_to_sec(g.end_time) - time_to_sec(g.start_time))),'%h:%i') as sessions");
			$query->addSelect("a.cancelled as status_cancelled,a.deleted as status_deleted,a.notes,concat(time_format(h.start_time,'%h:%i %p'),'-',time_format(h.end_time,'%h:%i %p')) as schedules,a.cost,date_format(e.start_date,'%m/%d/%Y') as date_begins,concat(i.name,' ',e.season_period_year) as periods,k.id as invoice_id,d.id as registration_month_id,0 as charge_id");
			$query->addFrom(Registration::_tablename . " as a");
			$query->addJoin(" left join " . RegistrationChild::_tablename . " as b on b.registration_id=a.id ");
			$query->addJoin(" left join " . ChildInfo::_tablename . " c on c.id=b.child_id ");
			$query->addJoin(" left join " . RegistrationMonth::_tablename . " as d  on d.registration_id=a.id ");
			$query->addJoin(" left join " . BaseCatalog::_tablename . " e on e.id = a.catalog_session_id ");
			$query->addJoin(" left join " . RegistrationDay::_tablename. " f on f.registration_id=a.id ");
			//$query->addJoin(" left join " . RegistrationDaytime::_tablename." g on g.registration_day_id= f.id ");
			$query->addJoin(" left join " . CatalogSchedule::_tablename ." h on h.catalog_session_id = a.catalog_session_id ");
			$query->addJoin(" left join " . SeasonPeriod::_tablename ." i on i.id=e.season_period_id ");
			$query->addJoin(" left join ". AccountingJournalRegistration::_tablename ." j on j.registration_id =  a.id ");
			$query->addJoin(" left join ". AccountingInvoice::_tablename ."  k on k.journal_id = j.accounting_journal_id" );
			
			$query->addWhere(" a.id in (" . $strregid . ") and k.id in (". $invoiceid .") and d.id in (". $regmonthid .") and b.registration_id > 0 and a.deleted = 0 ");
			$query->addGroupBy(" k.id,d.id order by a.id");		
			
			
			$query2 = new QueryCreator();
			$query2->addSelect(" all 'M' as doctype,a.child_id,a.id as reg_id,a.id as reg_line_child,date_format(b.transaction_date,'%m/%d/%Y') as reg_date_logs,'' as start_date,'' as end_day,0 as class_number,0 as session_id,concat('Miscllaneous Charge - ',e.name) as session_name,'' as catalog_session_id ");
			$query2->addSelect(" concat(firstname, ' ',left(middlename,1),'. ',lastname) as name,if(b.cancelled_date is null,0,1) as status_cancelled,0 as status_deleted,d.notes,'' as schedules,a.amount as cost,'' as date_begins,'' as periods,d.id as invoice_id,0 as registration_month_id,a.charge_id");
			$query2->addFrom(AccountingJournalItemCharge::_tablename ." a ");
			$query2->addJoin(" left join ". AccountingJournal::_tablename ." b on a.accounting_journal_id = b.id");
			$query2->addJoin(" left join ". ChildInfo::_tablename ." c on c.id = a.child_id ");
			$query2->addJoin(" left join ". AccountingInvoice::_tablename ." d on d.journal_id=b.id ");
			$query2->addJoin(" left join ". MiscellaneousCharge::_tablename ." e on e.id = a.charge_id");
			$query2->addWhere(" b.cancelled_date is null and a.id in (". $strchargeid .")" );
			
			
			
		$db = DBCon::instance();
		$result = $db->executeQuery('(' . $query->createSQL() .') union ('. $query2->createSQL() .')' );
		return $result;
		
	}
	
	public function getBalance($family_id=0,$daterange=''){
	
		#formula = DEBIT AR - CREDIT AR  + REFUND 
		
		Zend_loader::loadClass('AccountingJournal');
		Zend_loader::loadClass('AccountingJournalItem');
		Zend_loader::loadClass('AccountingJournalRegistration');
		Zend_loader::loadClass("CodesUtility");
		Zend_Loader::loadClass("AccountCodes");
		
		$accountCodes = new AccountCodes();
		$accountCodesArray = $accountCodes->getAccountCodesArray("short_name", "account_code");
		
		$query = new QueryCreator();
		
		$query->addSelect("sum( if(a.cancelled_date is null,  if( b.account_code = ". $accountCodesArray[CodesUtility::ACCOUNT_CODE_AR]  ." and accounting_type = ". CodesUtility::ACCOUNTING_TYPE_DEBIT .",b.cost, if( b.account_code = ". $accountCodesArray[CodesUtility::ACCOUNT_CODE_AR]  ." and accounting_type = ". CodesUtility::ACCOUNTING_TYPE_CREDIT .",-b.cost, 0 ) ) , 0 ) ) as balance");
		
		#credit on acount
		$query->addSelect(" sum((if(b.account_code = ". $accountCodesArray[CodesUtility::ACCOUNT_CODE_COA] ." and accounting_type = ". CodesUtility::ACCOUNTING_TYPE_CREDIT.",b.cost,0) ) - (if(b.account_code = ". $accountCodesArray[CodesUtility::ACCOUNT_CODE_COA] ." and accounting_type = ". CodesUtility::ACCOUNTING_TYPE_DEBIT .",b.cost,0)) ) as credit_on_account ");
		$query->addSelect("sum( if(b.account_code = ". $accountCodesArray[CodesUtility::ACCOUNT_CODE_COA]." and accounting_type = ". CodesUtility::ACCOUNTING_TYPE_CREDIT .",b.cost,0)   ) as total_credit_on_account");
		
		$query->addSelect("sum( if(b.account_code = ". $accountCodesArray[CodesUtility::ACCOUNT_CODE_COA]." and accounting_type = ". CodesUtility::ACCOUNTING_TYPE_DEBIT .",b.cost,0)   ) as total_coa_refund");
		
		$query->addFrom(AccountingJournal::_tablename ." a ");
		$query->addJoin(" left join ". AccountingJournalItem::_tablename ." b on a.id = b.journal_id ");
		$query->addWhere(" b.account_code in ('". $accountCodesArray[CodesUtility::ACCOUNT_CODE_AR] ."','". $accountCodesArray[CodesUtility::ACCOUNT_CODE_RF]."','".$accountCodesArray[CodesUtility::ACCOUNT_CODE_COA]."','".$accountCodesArray[CodesUtility::ACCOUNT_CODE_CIB]."')  " );
		if($family_id){
			$query->addWhere(" family_account_id = ". $family_id );
		} 
		
		if($daterange != ''){
			if(is_Array($daterange)){
				$query->addWhere(" date_format(a.transaction_date,'%Y-%m-%d') between '". $daterange[0] ."' and '". $daterange[1]."'");
			} else {
				$query->addWhere(" date_format(a.transaction_date,'%Y-%m-%d') <= '". $daterange ."'");
			}
		}
		if($family_id){
			$query->addGroupBy(" family_account_id  ");
		}
	//echo $query->createSQL();
		$db = DBCon::instance();
		$result = $db->executeQuery($query->createSQL());
		return $result;
	}
	
	public function getFamilyTax($year,$payerid,$location,$report_type){
		
		Zend_loader::loadClass("AccountingJournalItem");
		Zend_loader::loadClass("AccountCodes");
		Zend_loader::loadClass("CodesUtility");
		Zend_loader::loadClass("AccountingJournal");
		Zend_loader::loadClass("AccountingPaymentInvoice");
		Zend_loader::loadClass("AccountingJournalRegistration");
		Zend_loader::loadClass("Registration");
		Zend_loader::loadClass("CatalogLocation");
		Zend_loader::loadClass("Payer");
		Zend_loader::loadClass("Location");
		Zend_loader::loadClass("RegistrationChild");
		Zend_loader::loadClass("ChildInfo");
		Zend_loader::loadClass("SchedulingLocation");
		Zend_loader::loadClass("AccountingRefund");
		Zend_loader::loadClass("AccountingJournalItemCharge");
		Zend_Loader::loadClass("SchedulingRegistrationChildLocation");
		Zend_Loader::loadClass("AccountingPayment");
		
		$data = array();
		
		$accountcodes = new AccountCodes();
		$accountcodes->loadWhere(" short_name = '". CodesUtility::ACCOUNT_CODE_CIB ."'");
		$account = $accountcodes->toArray();
		
		$query = new QueryCreator();
		$query->addSelect(" a.id as payment_id,d.location_id,e.firstname,e.lastname,f.name as location_name,i.firstname as childfirstname,i.lastname as childlastname,sum(amount) - ifnull((select sum(amount) from ".AccountingRefund::_tablename." rf left join ". AccountingJournal::_tablename." aj on rf.journal_id=aj.id left join ". AccountingJournalRegistration::_tablename ." ajir on ajir.accounting_journal_id=rf.journal_id where aj.cancelled_date is null and ajir.registration_id = b.registration_id ),0)  as tax,monthname(g.transaction_date) as monthname");
		$query->addFrom(AccountingPayment::_tablename . " a ");
		$query->addJoin(" left join ". AccountingJournalRegistration::_tablename ." b on b.accounting_journal_id = a.journal_id ");
		$query->addJoin(" left join ". Registration::_tablename . " c on c.id = b.registration_id ");
		$query->addJoin(" left join ". RegistrationChild::_tablename . " h on h.registration_id = c.id");
		$query->addJoin(" left join ". SchedulingRegistrationChildLocation::_tablename . " j on j.registration_child_id=h.id" );
		$query->addJoin(" left join ". SchedulingLocation::_tablename . " d on d.catalog_session_id = c.catalog_session_id and d.id=j.scheduling_location_id");
		$query->addJoin(" left join ". Payer::_tablename . " e on e.id = a.payer_id ");
		$query->addJoin(" left join ". Location::_tablename . " f on f.id = d.location_id ");
		$query->addJoin(" left join ". AccountingJournal::_tablename . " g on g.id = a.journal_id ");
		$query->addJoin(" left join ". ChildInfo::_tablename . " i on i.id = h.child_id");
		$query->addWhere(" year(transaction_date) = ". $year);
		if(is_array($payerid)  ){
			if($payerid[0] != 'All' && $payerid[0] != '' ){
				$query->addWhere(" a.payer_id in (". implode($payerid,','). ")");
			}
		}
		if(is_array($location)){
			if($location[0] != 'All'  && $location[0] != '' ) {
				$query->addWhere(" d.location_id in (". implode($location) . ")");
			}
		}
		$query->addWhere(" b.amount >0 and g.cancelled_date is null");
		if($report_type ==1) {
			$query->addGroupby(" d.location_id,h.child_id ");
		} else {
			$query->addGroupby(" d.location_id,h.child_id,month(g.transaction_date) order by month(g.transaction_date) ");
		}
		
		$query2 = new QueryCreator();
		$query2->addSelect(" a.id as payment_id,d.location_id,e.firstname,e.lastname,f.name as location_name,i.firstname as childfirstname,i.lastname as childlastname,sum(amount) - ifnull((select sum(amount) from ".AccountingRefund::_tablename." rf left join ". AccountingJournal::_tablename." aj on rf.journal_id=aj.id left join ". AccountingJournalItemCharge::_tablename ." ajir on ajir.accounting_journal_id=rf.journal_id where aj.cancelled_date is null and ajir.registration_id = b.registration_id ),0)  as tax,monthname(g.transaction_date) as monthname");
		$query2->addFrom(AccountingPayment::_tablename . " a ");
		$query2->addJoin(" left join ". AccountingJournalItemCharge::_tablename ." b on b.accounting_journal_id = a.journal_id ");
		$query2->addJoin(" left join ". Registration::_tablename . " c on c.id = b.registration_id ");
		$query2->addJoin(" left join ". SchedulingLocation::_tablename . " d on d.catalog_session_id = c.catalog_session_id ");
		$query2->addJoin(" left join ". Payer::_tablename . " e on e.id = a.payer_id ");
		$query2->addJoin(" left join ". Location::_tablename . " f on f.id = d.location_id ");
		$query2->addJoin(" left join ". AccountingJournal::_tablename . " g on g.id = a.journal_id ");
		$query2->addJoin(" left join ". RegistrationChild::_tablename . " h on h.registration_id = c.id");
		$query2->addJoin(" left join ". ChildInfo::_tablename . " i on i.id = h.child_id");
		$query2->addWhere(" year(transaction_date) = ". $year);
		if(is_array($payerid) ){
			if($payerid[0] != 'All' && $payerid[0] != '' ){
				$query2->addWhere(" a.payer_id in (". implode($payerid,','). ")");
			}
		}
		if(is_array($location)){
			if($location[0] != 'All' && $location[0] != '' ){
				$query2->addWhere(" d.location_id in (". implode($location) . ")");
			}
		}
		$query2->addWhere(" b.amount >0 and g.cancelled_date is null");
		if($report_type ==1) {
			$query2->addGroupby(" d.location_id,h.child_id ");
		} else {
			$query2->addGroupby(" d.location_id,h.child_id,month(g.transaction_date) order by month(g.transaction_date) ");
		}
		
		
		//echo '('.$query->createSQL() .') union ('. $query2->createSQL() .')';die();
		$db = DBCon::instance();	
		$result = $db->executeQuery('('.$query->createSQL() .') union ('. $query2->createSQL() .')');
		
		/*  [payerid]  - [location_id] - details */
		foreach($result as $key => $value){
			
			if($report_type == 1) {
				if(isset($data[$value['location_name']][$value['lastname'].','.$value['firstname']][$value['childfirstname'].' '.$value['childlastname']])) {
					$data[$value['location_name']][$value['lastname'].','.$value['firstname']][$value['childfirstname'].' '.$value['childlastname']] = $value['tax'] + $data[$value['location_name']][$value['lastname'].','.$value['firstname']][$value['childfirstname'].' '.$value['childlastname']];
				} else {
					$data[$value['location_name']][$value['lastname'].','.$value['firstname']][$value['childfirstname'].' '.$value['childlastname']] = $value['tax'];
				} 
				
			} else {
				if(isset($data[$value['lastname'].','.$value['firstname']][$value['location_name']][$value['monthname']])){
					$data[$value['lastname'].','.$value['firstname']][$value['location_name']][$value['monthname']] = $value['tax'] + $data[$value['lastname'].','.$value['firstname']][$value['location_name']][$value['monthname']];
				} else {
					$data[$value['lastname'].','.$value['firstname']][$value['location_name']][$value['monthname']] = $value['tax'];
				}
				
				
				
				if(isset($data[$value['lastname'].','.$value['firstname']][$value['location_name']]['students'])){
					$data[$value['lastname'].','.$value['firstname']][$value['location_name']]['students'] = $value['childfirstname'].' '.$value['childlastname'] .','.$data[$value['lastname'].','.$value['firstname']][$value['location_name']]['students'];
				} else {
					$data[$value['lastname'].','.$value['firstname']][$value['location_name']]['students'] = $value['childfirstname'].' '.$value['childlastname'];
				}
				
				
			}
			
		} 
		//print_r($data);
		return $data;
		
		
	}
	
	public function getTransactionDetails($param = array()){
		// load the transaction 
		//payment , registration  , refund via cash, refund via credit
		
		Zend_loader::loadClass("AccountingJournal");
		Zend_loader::loadClass("AccountingPayment");
		Zend_loader::loadClass("AccountingRefund");
		Zend_loader::loadClass("AccountingCreditAccount");
		Zend_loader::loadClass("AccountingJournalRegistration");
		Zend_loader::loadClass("Registration");
		Zend_loader::loadClass("BaseCatalog");
		Zend_loader::loadClass("AccountCode");
		Zend_loader::loadClass("Payer");
		Zend_loader::loadClass("AccountingPaymentCard");
		Zend_loader::loadClass("AccountingPaymentCash");
		Zend_loader::loadClass("PayerCard");
		Zend_loader::loadClass("PayerCardType");
		Zend_loader::loadClass("AccountingRefund");
		Zend_loader::loadClass("CatalogLocation");
		Zend_loader::loadClass("RegistrationChild");
		Zend_loader::loadClass("ChildInfo");
		Zend_loader::loadClass("ChildAddress");
		Zend_loader::loadClass("AddressType");
		Zend_loader::loadClass("AccountingJournalItem");
		Zend_loader::loadClass("AccountCodes");
		Zend_loader::loadClass("AccountingJournalItemCharge");
		//Zend_loader::loadClass("BaseSession");
		
		//print_r($param);
		
		$accountCodes = new AccountCodes();
		$accountCodesArray = $accountCodes->getAccountCodesArray("short_name", "account_code");
		
		$display_array = array();
		$rtype_array = array();
		$rtype = '';
		$registration = 0;
		if(isset($param['display_payment'])){
			foreach($param['display_payment'] as $key => $value){
				$display_array[] = "'".$value."'";
			}
			$display = implode(',',$display_array);
		} else {
			$display = '';
		}
		
		if(isset($param['display_transaction'])){
			foreach($param['display_transaction'] as $key => $value){
				if($value == 'Registration' ) {
					$registration = 1;
				}
				
				if($value == 'registrationpaidviacredit'){
					if($display != ''){
						$display .= ',"paidviacredit"';
					} else {
						$display = '"paidviacredit"';
					}
				}
				if($value == 'refunds' || $value == 'refundsleftonacct'){
					$rtype_array[] = "'".$value."'";
				}
			}
			if(count($rtype_array) > 0) {
				$rtype = implode(',',$rtype_array);
			} else {
				$rtype = '';
			}
			
		} 
		
		
				$query_Payments = new QueryCreator();
				$query_Payments->addSelect("date_format(a.transaction_date,'%m/%d/%Y') as tdate,date_format(a.transaction_date,'%I:%i %p') as ttime");
				$query_Payments->addSelect("'Registration' as ttype,g.firstname,g.lastname,f.short_name as account_code,if(i.accounting_payment_id > 0,if(i.check_number is not null and i.check_number != '',concat('Check#',i.check_number),'Cash'),ifnull(concat(k.value,' ', concat('-',right(j.card_number,4) )),'Credit on Account')  ) as paymenttype ");
				$query_Payments->addSelect(" sum(ifnull(c.amount,c1.amount)) as amount");
				$query_Payments->addSelect(" case when (i.accounting_payment_id >0 and (i.check_number='' or i.check_number is null) ) then 'cash' when h.accounting_payment_id > 0 then 'creditcard' when (i.accounting_payment_id >0 and (i.check_number!='' or i.check_number is not null) ) then 'check' when (i.accounting_payment_id is null and h.accounting_payment_id is null) then 'paidviacredit'  end as ptype,e.name as caresession_name ");
				if($param['reportype'] != 'transaction') {
					$query_Payments->addSelect(" n.firstname as childfirstname,n.lastname as childlastname,o.address,o.city,o.state,o.zip,o.phone");
				}
				$query_Payments->addSelect("'' as rtype");
				$query_Payments->addFrom(AccountingJournal::_tablename ." a ");
				//payment
				$query_Payments->addJoin(" left join ". AccountingPayment::_tablename . " b  on a.id=b.journal_id ");
				$query_Payments->addJoin(" left join ". AccountingJournalRegistration::_tablename ." c on c.accounting_journal_id=b.journal_id ");
				$query_Payments->addJoin(" left join ". AccountingJournalItemCharge::_tablename ." c1 on c1.accounting_journal_id=b.journal_id ");
				$query_Payments->addJoin(" left join ". Registration::_tablename ." d on d.id=ifnull(c.registration_id,c1.registration_id) " );
				$query_Payments->addJoin(" left join ". BaseCatalog::_tablename . " e on e.id = d.catalog_session_id " );
				$query_Payments->addJoin(" left join ". AccountCode::_tablename . " f on f.id=e.account_code_id " );
				$query_Payments->addJoin(" left join ". Payer::_tablename . " g on g.id=b.payer_id");
				$query_Payments->addJoin(" left join ". AccountingPaymentCard::_tablename ." h on h.accounting_payment_id=b.id");
				$query_Payments->addJoin(" left join ". AccountingPaymentCash::_tablename ." i on i.accounting_payment_id=b.id");
				$query_Payments->addJoin(" left join ". PayerCard::_tablename . " j on h.payer_card_id=j.id ");
				$query_Payments->addJoin(" left join ". PayerCardType::_tablename . " k on j.card_type_id=k.id" );
				
				if($param['reportype'] != 'transaction') {
				
					$query_Payments->addJoin(" left join ". RegistrationChild::_tablename ." m on m.registration_id=d.id");
					$query_Payments->addJoin(" left join ". ChildInfo::_tablename . " n on n.id=m.child_id ");
					$query_Payments->addJoin(" left join ". ChildAddress::_tablename ." o on o.child_id = n.id ");
					$query_Payments->addJoin(" left join ". AddressType::_tablename ." p on p.id=o.address_type_id ");
				}
				// check if the payment is from credit on account
				$query_Payments->addJoin(" left join ". AccountingCreditAccount::_tablename ." q on q.journal_id = a.id " );
				
				
				if(isset($param['location'])){
					$query_Payments->addJoin(" left join ". CatalogLocation::_tablename ." l on l.catalog_session_id = e.id ");
					$query_Payments->addWhere(" l.location_id = ". $param['location']);
				}
				
				if($param['reportype'] != 'transaction') {
					$query_Payments->addWhere(" p.code = '". CodesUtility::PRIMARY_ADDRESS ."'");
				}
				
				$query_Payments->addWhere(" a.cancelled_date is null ");
				if(isset($param['daterange'])){
					$query_Payments->addWhere(" date_format(a.transaction_date,'%Y-%m-%d') between '". $param['daterange'][0]. "' and '". $param['daterange'][1]."'");
				}
				if(isset($param['account_code'])){
					$query_Payments->addWhere(" e.account_code_id = ". $param['account_code']);
				}
				if(isset($param['payer'])){
					$query_Payments->addWhere("b.payer_id = ". $param['payer']);
				}
				
				if(isset($param['program'])){
					$query_Payments->addWhere(" e.session_program_id = ". $param['program']);
				}
				if(isset($param['category'])){
					$query_Payments->addWhere(" e.session_category_id = ". $param['category']);
				}
				if(isset($param['caresession'])){
					$query_Payments->addWhere(" e.session_id = ". $param['caresession']);
				}
				
				/*if(isset($param['groupby']))	{		
					foreach($param['groupby'] as $key => $value)   {
						if($value == 'accountcode') {
							$query_Payments->addGroupBy(" f.account_code ");	
						}
						if($value == 'caresession'){
							$query_Payments->addGroupBy("d.catalog_session_id");
						}
						if($value == 'payername'){
							$query_Payments->addGroupBy("b.payer_id");
						}
					}
					
				} else {
					$query_Payments->addGroupBy(" a.id ");
				}*/
				$query_Payments->addGroupBy(" a.id ");
				$query_Payments->addHaving('amount > 0');
				if($display != ''){
					$query_Payments->addHaving(" ptype in (". $display.")");
				} else {
					$query_Payments->addHaving(" ptype = 'none'");
				}
				
				if($registration == 0){
					$query_Payments->addHaving(" ptype = 'none'");
				}
				//echo $query_Payments->createSQL();
		
		//refunds 
		$query_Refunds = new QueryCreator();
		$query_Refunds->addSelect("date_format(a.transaction_date,'%m/%d/%Y') as tdate,date_format(a.transaction_date,'%I:%i %p') as ttime");
		$query_Refunds->addSelect("if(q.id > 0,'Refunds Left on Account','Refunds for Cancellation') as ttype,d.firstname,d.lastname,g.short_name as account_code");
		$query_Refunds->addSelect(" if(i.accounting_payment_id > 0,'Cash',concat(k.value,' ', concat(repeat('X',length(j.card_number)-4),'-',right(j.card_number,4) ))  ) as paymenttype");
		$query_Refunds->addSelect(" sum(c.amount) * if(q.id > 0,1,-1) as amount");
		$query_Refunds->addSelect(" CASE when (i.accounting_payment_id >0 and (i.check_number='' or i.check_number is null) ) then 'cash' when h.accounting_payment_id > 0 then 'creditcard' when (i.accounting_payment_id >0 and (i.check_number!='' or i.check_number is not null)) then 'check' when (i.accounting_payment_id is null and h.accounting_payment_id is null) then 'paidviacredit'  END as ptype ");
		if($param['reportype'] != 'transaction') {
			$query_Refunds->addSelect(" n.firstname as childfirstname,n.lastname as childlastname,o.address,o.city,o.state,o.zip,o.phone");
		}
		$query_Refunds->addSelect("if(q.id > 0,'refundsleftonacct','refunds') as rtype,f.name as caresession_name");
		$query_Refunds->addFrom(AccountingJournal::_tablename ." a ");
		$query_Refunds->addJoin(" left join ". AccountingRefund::_tablename ." b on a.id = b.journal_id" );
		$query_Refunds->addJoin(" left join ". AccountingJournalRegistration::_tablename ." c on c.accounting_journal_id = b.journal_id");
		$query_Refunds->addJoin(" left join ". Payer::_tablename ." d on d.id=b.payer_id ");
		$query_Refunds->addJoin(" left join ". Registration::_tablename . " e on e.id = c.registration_id ");
		$query_Refunds->addJoin(" left join ". BaseCatalog::_tablename . " f on f.id=e.catalog_session_id ");
		$query_Refunds->addJoin(" left join ". AccountCode::_tablename . " g on g.id = f.account_code_id " );
		$query_Refunds->addJoin(" left join ". AccountingPaymentCard::_tablename ." h on h.accounting_payment_id = b.accounting_payment_id ");
		$query_Refunds->addJoin(" left join ". AccountingPaymentCash::_tablename ." i on i.accounting_payment_id = b.accounting_payment_id ");
		$query_Refunds->addJoin(" left join ". PayerCard::_tablename ." j on j.id=h.payer_card_id ");
		$query_Refunds->addJoin(" left join ". PayerCardType::_tablename ." k on k.id=j.card_type_id ");
		
		
		if($param['reportype'] != 'transaction') {
		
			$query_Refunds->addJoin(" left join ". RegistrationChild::_tablename ." m on m.registration_id=d.id");
			$query_Refunds->addJoin(" left join ". ChildInfo::_tablename . " n on n.id=m.child_id ");
			$query_Refunds->addJoin(" left join ". ChildAddress::_tablename ." o on o.child_id = n.id ");
			$query_Refunds->addJoin(" left join ". AddressType::_tablename ." p on p.id=o.address_type_id ");
		}
		// Join journal details to see if the refund is left on  account as coa 
		$query_Refunds->addJoin(" left join ". AccountingJournalItem::_tablename . " q on q.journal_id = a.id and q.account_code = '". $accountCodesArray[CodesUtility::ACCOUNT_CODE_COA] ."' and accounting_type = ". CodesUtility::ACCOUNTING_TYPE_CREDIT);
		
		if(isset($param['location'])){
			$query_Refunds->addJoin(" left join ". CatalogLocation::_tablename ." l on l.catalog_session_id = f.id ");
			$query_Refunds->addWhere(" l.location_id = ". $param['location']);
		}
		
		if($param['reportype'] != 'transaction') {
			$query_Refunds->addWhere(" p.code = '". CodesUtility::PRIMARY_ADDRESS ."'");
		}
		
		$query_Refunds->addWhere(" a.cancelled_date is null and a.transaction_type = 3 ");
		
		if(isset($param['daterange'])){
			$query_Refunds->addWhere(" date_format(a.transaction_date,'%Y-%m-%d') between '". $param['daterange'][0]. "' and '". $param['daterange'][1]."'");
		}
		if(isset($param['account_code'])){
			$query_Refunds->addWhere(" f.account_code_id = ". $param['account_code']);
		}
		if(isset($param['payer'])){
			$query_Refunds->addWhere("b.payer_id = ". $param['payer']);
		}
		if(isset($param['program'])){
			$query_Refunds->addWhere(" f.session_program_id = ". $param['program']);
		}
		if(isset($param['category'])){
			$query_Refunds->addWhere(" f.session_category_id = ". $param['category']);
		}
		if(isset($param['caresession'])){
			$query_Refunds->addWhere(" f.session_id = ". $param['caresession']);
		}
		
		
		/*if(isset($param['groupby']))	{		
			foreach($param['groupby'] as $key => $value)   {
				if($value == 'accountcode') {
					$query_Refunds->addGroupBy(" g.account_code ");	
				}
				if($value == 'caresession'){
					$query_Refunds->addGroupBy("e.catalog_session_id");
				}
				if($value == 'payername'){
					$query_Refunds->addGroupBy("b.payer_id");
				}
			}
					
		} else {
					$query_Payments->addGroupBy(" a.id ");
		}*/
		$query_Refunds->addGroupBy(" a.id ");
		if($display != ''){
			$query_Refunds->addHaving(" ptype in (". $display.")");
		}
		if($rtype != ''){
			$query_Refunds->addHaving(" rtype in (". $rtype .")");
		} else {
			$query_Refunds->addHaving(" rtype = 'none'");
		}
		//echo '('.$query_Payments->createSQL() .') union ('. $query_Refunds->createSQL() .')  order by tdate';
		//die();
		//echo $query_Refunds->createSQL();
		
		if(isset($param['orderby'])){
			$orderby = $param['orderby'];
		} else {
			$orderby = 'tdate';
		}
		
		$db = DBCon::instance();
		if($param['reportype'] != 'transaction') {
			$result = $db->executeQuery($query_Refunds->createSQL() .'  order by '. $orderby);
		} else {
			$result = $db->executeQuery('('.$query_Payments->createSQL() .') union all ('. $query_Refunds->createSQL() .')  order by '. $orderby);
		}
		
		return $result;
	}
	
	public function getrefundsandcredit($param){
		
		Zend_loader::loadClass("AccountingJournal");
		Zend_loader::loadClass("AccountingPayment");
		Zend_loader::loadClass("AccountingRefund");
		Zend_loader::loadClass("AccountingCreditAccount");
		Zend_loader::loadClass("AccountingJournalRegistration");
		Zend_loader::loadClass("Registration");
		Zend_loader::loadClass("BaseCatalog");
		Zend_loader::loadClass("AccountCode");
		Zend_loader::loadClass("Payer");
		Zend_loader::loadClass("AccountingPaymentCard");
		Zend_loader::loadClass("AccountingPaymentCash");
		Zend_loader::loadClass("PayerCard");
		Zend_loader::loadClass("PayerCardType");
		Zend_loader::loadClass("AccountingRefund");
		Zend_loader::loadClass("CatalogLocation");
		Zend_loader::loadClass("RegistrationChild");
		Zend_loader::loadClass("ChildInfo");
		Zend_loader::loadClass("ChildAddress");
		Zend_loader::loadClass("AddressType");
		Zend_loader::loadClass("AccountingJournalItem");
		Zend_loader::loadClass("AccountCodes");
		Zend_loader::loadClass("AccountingJournalItemCharge");
		
		//print_r($param);
		$display_refunds = '';
		$display_refund_array = array();
		if(isset($param['display_refunds'])){
			foreach($param['display_refunds'] as $key => $value){
				$display_refund_array[] = "'".$value."'";
			}
			$display_refunds = implode(',',$display_refund_array);
		}
		
		
		
		$accountCodes = new AccountCodes();
		$accountCodesArray = $accountCodes->getAccountCodesArray("short_name", "account_code");
		
		$query_Refunds = new QueryCreator();
		$query_Refunds->addSelect("date_format(a.transaction_date,'%m/%d/%Y') as tdate,date_format(a.transaction_date,'%I:%i %p') as ttime");
		$query_Refunds->addSelect("if(q.id > 0,'Refunds Left on Account','Refunds for Cancellation') as ttype,d.firstname,d.lastname,g.short_name as account_code");
		$query_Refunds->addSelect(" if(i.accounting_payment_id > 0 and (i.check_number is null or i.check_number =''),'Cash',ifnull(concat(k.value,' ', concat(repeat('X',length(j.card_number)-4),'-',right(j.card_number,4) )),concat('Check #',i.check_number) )   ) as paymenttype");
		$query_Refunds->addSelect(" sum(ifnull(c.amount,c1.amount)) as amount");
		$query_Refunds->addSelect(" CASE when (i.accounting_payment_id >0 and (i.check_number='' or i.check_number is null) ) then 'cash' when h.accounting_payment_id > 0 then 'creditcard' when (i.accounting_payment_id >0 and (i.check_number!='' or i.check_number is not null)) then 'check' when (i.accounting_payment_id is null and h.accounting_payment_id is null) then 'paidviacredit'  END as ptype ");
		$query_Refunds->addSelect(" n.firstname as childfirstname,n.lastname as childlastname,o.address,o.city,o.state,o.zip,o.phone");
		$query_Refunds->addSelect("if(q.id > 0,'refundsleftonacct','refunds') as rtype");
		$query_Refunds->addSelect("f.name as programname,datediff(a.transaction_date,s.transaction_date) as duration,b.payer_id");
		$query_Refunds->addSelect("d.address as payer_address");
		
		$query_Refunds->addFrom(AccountingJournal::_tablename ." a ");
		$query_Refunds->addJoin(" left join ". AccountingRefund::_tablename ." b on a.id = b.journal_id" );
		$query_Refunds->addJoin(" left join ". AccountingJournalRegistration::_tablename ." c on c.accounting_journal_id = b.journal_id");
		$query_Refunds->addJoin(" left join ". AccountingJournalItemCharge::_tablename . " c1 on c1.accounting_journal_id=b.journal_id");
		$query_Refunds->addJoin(" left join ". Payer::_tablename ." d on d.id=b.payer_id ");
		$query_Refunds->addJoin(" left join ". Registration::_tablename . " e on e.id = ifnull(c.registration_id,c1.registration_id) ");
		$query_Refunds->addJoin(" left join ". BaseCatalog::_tablename . " f on f.id=e.catalog_session_id ");
		$query_Refunds->addJoin(" left join ". AccountCode::_tablename . " g on g.id = f.account_code_id " );
		$query_Refunds->addJoin(" left join ". AccountingPaymentCard::_tablename ." h on h.accounting_payment_id = b.accounting_payment_id ");
		$query_Refunds->addJoin(" left join ". AccountingPaymentCash::_tablename ." i on i.accounting_payment_id = b.accounting_payment_id ");
		$query_Refunds->addJoin(" left join ". PayerCard::_tablename ." j on j.id=h.payer_card_id ");
		$query_Refunds->addJoin(" left join ". PayerCardType::_tablename ." k on k.id=j.card_type_id ");
		$query_Refunds->addJoin(" left join ". RegistrationChild::_tablename ." m on m.registration_id=e.id");
		$query_Refunds->addJoin(" left join ". ChildInfo::_tablename . " n on n.id=m.child_id ");
		$query_Refunds->addJoin(" left join ". ChildAddress::_tablename ." o on o.child_id = n.id ");
		$query_Refunds->addJoin(" left join ". AddressType::_tablename ." p on p.id=o.address_type_id ");
		$query_Refunds->addJoin(" left join ". AccountingPayment::_tablename ."  r on r.id=b.accounting_payment_id ");
		$query_Refunds->addJoin(" left join ". AccountingJournal::_tablename ." s on s.id=r.journal_id");
		// Join journal details to see if the refund is left on  account as coa 
		$query_Refunds->addJoin(" left join ". AccountingJournalItem::_tablename . " q on q.journal_id = a.id and q.account_code = '". $accountCodesArray[CodesUtility::ACCOUNT_CODE_COA] ."' and accounting_type = ". CodesUtility::ACCOUNTING_TYPE_CREDIT);
	
		
		if(isset($param['location'])){
			$query_Refunds->addJoin(" left join ". CatalogLocation::_tablename ." l on l.catalog_session_id = f.id ");
			$query_Refunds->addWhere(" l.location_id = ". $param['location']);
		}
		
		if($param['reportype'] != 'transaction') {
			$query_Refunds->addWhere(" (p.code = '". CodesUtility::PRIMARY_ADDRESS ."' or p.code is null)");
		}
		
		$query_Refunds->addWhere(" a.cancelled_date is null and a.transaction_type = 3 ");
		
		if(isset($param['daterange'])){
			$query_Refunds->addWhere(" date_format(a.transaction_date,'%Y-%m-%d') between '". $param['daterange'][0]. "' and '". $param['daterange'][1]."'");
		}
		if(isset($param['account_code'])){
			$query_Refunds->addWhere(" f.account_code_id = ". $param['account_code']);
		}
		if(isset($param['payer'])){
			$query_Refunds->addWhere("b.payer_id = ". $param['payer']);
		}
		if(isset($param['program'])){
			$query_Refunds->addWhere(" f.session_program_id = ". $param['program']);
		}
		if(isset($param['category'])){
			$query_Refunds->addWhere(" f.session_category_id = ". $param['category']);
		}
		if(isset($param['caresession'])){
			$query_Refunds->addWhere(" f.session_id = ". $param['caresession']);
		}
		
		if($param['display_transaction'] == 'credit'){
			$query_Refunds->addWhere(" q.id > 0");
		}
		
		if($param['display_transaction'] == 'refund'){
			$query_Refunds->addWhere(" q.id is null ");
		}
		
		
		$query_Refunds->addGroupBy(" a.id");
		if($display_refunds != '') {
			$query_Refunds->addHaving(" ptype in (". $display_refunds .")");
			
		} else {
			$query_Refunds->addHaving(" ptype = 'none'");
		}
	
		if(isset($param['orderby'])){
			if(is_Array($param['orderby'])){
				$orderby = implode(',',$param['orderby']);
			} else {
				$orderby = $param['orderby'];
			}
		} else {
			$orderby = 'tdate';
		}
		//echo $query_Refunds->createSQL();
		$db = DBCon::instance();
		$result = $db->executeQuery($query_Refunds->createSQL() .'  order by '. $orderby);
		
		return $result;
	}
	
	public function getInvoicePaymentDetails($param){
	
		Zend_Loader::loadClass("AccountingJournal");
		Zend_Loader::loadClass("AccountingJournalRegistration");
		Zend_Loader::loadClass("RegistrationMonth");
		Zend_Loader::loadClass("AccountingInvoice");
		Zend_Loader::loadClass("AccountingPayment");
		Zend_Loader::loadClass("RegistrationAdditionalCost");
	
		$details = array();
		$data = array();
		foreach($param as $key => $value){
			if($value['doctype'] == 'I') {
				$details[] = $value['line_id'];
			}
			if($value['doctype'] == 'P' ) {
				$details[] = $value['line_id'];
			}
		}
		
		if(count($details) > 1){
			$query = new QueryCreator();
			$query->addSelect(" a.id,b.accounting_invoice_id,a.transaction_type,c.year,concat(if(c.month > 0 and d.id is null and f.id is null,monthname(concat(c.year ,'-',c.month,'-1')),if(d.id is null and f.id is null,' Reg. Fee','')),if(d.id > 0 ,concat(' Added Day - ',date_format(d.scheduled_date,'%a. %b %e')) , if(f.id > 0, concat(' Added Day - ',date_format(f.scheduled_date,'%a. %b %e')) , if(c.year > 0,concat(' - ',c.year),'')) ) ) as month_name ");
			$query->addFrom(AccountingJournal::_tablename . " a ");
			$query->addJoin(" left join ". AccountingJournalRegistration::_tablename . " b on a.id = b.accounting_journal_id " );
			$query->addJoin(" left join ". RegistrationMonth::_tablename . " c on b.registration_month_id=c.id");
			$query->addJoin(" left join ". AccountingPayment::_tablename ." e on e.journal_id = a.id ");
			$query->addJoin(" left join ". RegistrationAdditionalCost::_tablename . " d on d.accounting_invoice_id = b.accounting_invoice_id and d.accounting_payment_id = e.id ");
			$query->addJoin(" left join ". RegistrationAdditionalCost::_tablename . " f on f.accounting_invoice_id = b.accounting_invoice_id and a.transaction_type = ". CodesUtility::TRANS_TYPE_INVOICE);
			$query->addWhere(" a.id in (". implode(',',$details) . ") and a.cancelled_date is null ");
			
			
			$db = DBCon::instance();
			$result = $db->executeQuery($query->createSQL());
			
			$count = 0;
			
			foreach($result as $key => $value) {
				$count = $count + 1;
				//print_r($value);
				if($value['transaction_type'] == CodesUtility::TRANS_TYPE_INVOICE)
					$data['invoice'][$value['accounting_invoice_id']][$value['month_name']] = $value;
				if($value['transaction_type'] == CodesUtility::TRANS_TYPE_PAYMENT)
					$data['payment'][$value['accounting_invoice_id']][$value['month_name']] = $value;
			}
			
			return $data;
		}
	}
	
	
	public function saveRecurringPayment($payer_card_id = null, $recurringsetupid = 0)
	{
		Zend_Loader::loadClass("CartUtility");
		Zend_Loader::loadClass("CodesUtility");
		Zend_Loader::loadClass("RegistrationMonth");
		Zend_Loader::loadClass("AccountingPaymentRecurring");
		Zend_Loader::loadClass("AccountingPaymentRecurringSetup");
		Zend_Loader::loadClass("TimeUtility");
		Zend_Loader::loadClass("Registration");
		
		$recCart = CartUtility::getRecurringCart();
		$items = $recCart->payschedule;
		$payperiod = $recCart->payperiod;
		if(!$payer_card_id){
			if(!$recCart->payer_card_id){
				return;
			}else{
				$payer_card_id = $recCart->payer_card_id;
			}
		}
			
		if($payperiod == CodesUtility::REC_PAY_PERIOD_MONTHLY){
			$multiplier = 1;
		}elseif($payperiod == CodesUtility::REC_PAY_PERIOD_WEEKLY){
			$multiplier = 4;
		}else{
			$multiplier = 2;
		}
		
		//echo "<pre>";print_r($items);echo "</pre>";
		//die();
		
		if($items){
			$registrationMonth = new RegistrationMonth();
			$registration = new Registration();
			$accountingPaymentRecurring = new AccountingPaymentRecurring();
			$recurringSetup = new AccountingPaymentRecurringSetup();
			
			if($recurringsetupid){
				$recurringSetup->loadById($recurringsetupid);
			}
			$recurringSetup->payer_card_id = $payer_card_id;
			$recurringSetup->payperiod = $payperiod;
			$recurringSetup->from_date =  TimeUtility::convertToSQLDateFormat($recCart->datefrom);
			$recurringSetup->to_date = TimeUtility::convertToSQLDateFormat($recCart->dateto);
			if($payperiod == CodesUtility::REC_PAY_PERIOD_BIWEEKLY){
				$recurringSetup->payday1 = $recCart->bifirstpayment;
			}else{
				$recurringSetup->payday1 = $recCart->mofirstpayment;
			}
			$recurringSetup->payday2 = $recCart->secondPaymentDay;
			$recurringSetup->payweekday = $recCart->dayofweek;
			$recurringSetup->deleted = 0;
			$recurringSetup->save();
			
			
			foreach($items as $item){
				if(isset($item['processed']) && $item['processed']){
					continue;
				}
				
				$month = date("m",strtotime($item['paydate']));
				$year = date("Y",strtotime($item['paydate']));
				$discount = $item['discount'];
				$scholarship = $item['scholarship'];
				$pricetierid = $item['pricetierid'];
				$totalAmount = $item['amount'];
				
				if($item['regBreakdown']){
					foreach($item['regBreakdown'] as $regItem){
						$regId = $regItem['regId'];
						if(!$regId){
							$registrationArray = $registration->getRegistration($regItem['catalog_session_id'],$regItem['child_id']);
							$regId = $registrationArray[0]['id'];
						}
						
						$regcost = $regItem['cost'] / $multiplier;
						$itemCost = 0;
						if($totalAmount > $regcost){
							$itemCost = $regcost;
							$totalAmount = $totalAmount - $regcost;
						}else{
							$itemCost = $totalAmount;
							$totalAmount = 0;
						}
						
						$registrationMonth->clear();
						$registrationMonth->loadRegistrationMonth($regId,$month,$year);
						$registrationMonth->discount = $discount * $multiplier;
						$registrationMonth->scholarship = $scholarship * $multiplier;
						$registrationMonth->price_tier_id = $pricetierid * $multiplier;
						$registrationMonth->save();
						
						if($itemCost > 0 ){
							$accountingPaymentRecurring->clear();
							if($regItem['id']){
								$accountingPaymentRecurring->loadById($regItem['id']);
							}
							$accountingPaymentRecurring->registration_month_id = $registrationMonth->id;
							$accountingPaymentRecurring->amount = $itemCost;
							$accountingPaymentRecurring->payment_date =  TimeUtility::convertToSQLDateFormat($item['paydate']);
							$accountingPaymentRecurring->recurring_setup_id = $recurringSetup->id;
							$accountingPaymentRecurring->deleted = 0;
							$accountingPaymentRecurring->save();
						}
						
					}
				}
			}
		}
		
		$recCart->payschedule = null;
		$recCart->items = null;
		$recCart->payperiod = null;
		$recCart->payer_card_id = null;
		
	}
	
	public function getRegistrationFuturePayment($family_account_id=0,$daterange=array(),$param=array()){
	
	
		Zend_Loader::loadClass("Registration");
		Zend_Loader::loadClass("RegistrationMonth");
		Zend_Loader::loadClass("AccountingJournal");
		Zend_Loader::loadClass("AccountingPayment");
		Zend_Loader::loadClass("AccountingJournalRegistration");
		Zend_Loader::loadClass("BaseCatalog");
		Zend_Loader::loadClass("CatalogDays");
		Zend_Loader::loadClass("TimeUtility");
		Zend_Loader::loadClass("CatalogSchedules");
		Zend_Loader::loadClass("RegistrationChild");
		Zend_Loader::loadClass("ChildInfo");
		Zend_Loader::loadClass("CatalogSessionPublicPaymentSetting");
		Zend_Loader::loadClass("CatalogCareSetting");
		Zend_Loader::loadClass("AccountingRefund");
		Zend_Loader::loadClass("RegistrationDays");
		Zend_Loader::loadClass("FamilyAccountPayer");
		Zend_Loader::loadClass("Payer");
		Zend_Loader::loadClass("PayerContact");
		
		$query = new QueryCreator();
		$query->addSelect("a.id, b.year, ifnull(concat(monthname(concat(b.year,'-',b.month,'-01')),', ',b.year),'Initial Registration') as mymonth,scholarship,discount,bc.name,a.catalog_session_id,b.id as registration_month_id,b.price_tier_id,a.family_account_id ");
		$query->addSelect("p.firstname as payer_firstname,p.lastname as payer_lastname,p.id as payerId");
		
		$query->addSelect(" b.cost - b.discount - b.scholarship - b.price_tier_amount - ifnull((select sum(ajr.amount) from ". AccountingInvoice::_tablename ." ap LEFT JOIN ". AccountingJournalRegistration::_tablename ." ajr on ap.journal_id = ajr.accounting_journal_id LEFT JOIN ". AccountingJournal::_tablename ." aj on aj.id=ap.journal_id where ajr.registration_month_id=b.id and a.id=ajr.registration_id and aj.cancelled_date is null ),0)  as amount   ");
		$query->addSelect("id_number,date_format(bc.start_date,'%m/%d/%Y') as start_date,b.month");
		$query->addSelect("ci.firstname,ci.lastname");
		$query->addSelect("allow_monthly,allow_biweekly,allow_weekly,allow_partial_payments as allow_partial");
		$query->addFrom(Registration::_tablename . " a ");
		$query->addJoin(" LEFT JOIN ". RegistrationMonth::_tablename . " b on a.id=b.registration_id ");
		$query->addJoin(" LEFT JOIN ". BaseCatalog::_tablename ." bc on bc.id=a.catalog_session_id ");
		$query->addJoin(" LEFT JOIN ". RegistrationChild::_tablename . " rc on rc.registration_id = a.id");
		$query->addJoin(" LEFT JOIN ". ChildInfo::_tablename . " ci on ci.id=rc.child_id");
		$query->addJoin(" LEFT JOIN ". CatalogSessionPublicPaymentSetting::_tablename . " csp on csp.catalog_session_id = a.catalog_session_id ");
		$query->addJoin(" LEFT JOIN ". CatalogCareSetting::_tablename . " ccs on ccs.catalog_session_id = a.catalog_session_id");
		if($family_account_id) 
			$query->addWhere(" a.family_account_id = ". $family_account_id );
		if(isset($daterange[1]))  {
			$start_date = $daterange[0];
			$end_date= $daterange[1];
			$query->addWhere("concat(b.year,'-',lpad(b.month,2,'0')) between '". date("Y-m",strtotime($start_date)) . "' and '". date("Y-m",strtotime($end_date)) ."'" );
		}
		
		if(count($param)) {
		
			if(isset($param['program'])){
				$query->addWhere(" bc.session_program_id = " . $param['program'] );
			}
			if(isset( $param['caresession'] )) {
				$query->addWhere("  a.catalog_session_id = ". $param['caresession'] );
			}
			
			if(isset( $param['payer'] )) {
				
			  $query->addJoin(" LEFT JOIN ". FamilyAccountPayer::_tablename . " fap on fap.family_account_id = a.family_account_id and payer_id = ". $param['payer'] );
			  $query->addJoin(" LEFT JOIN ". Payer::_tablename . " p on p.id = fap.payer_id " );
			 
			  $query->addWhere(" fap.payer_id = ". $param['payer'] );
			}  else {
				Zend_Loader::loadClass("FamilyAccountPayer");
				$query->addJoin(" LEFT JOIN ". FamilyAccountPayer::_tablename . " fap on fap.family_account_id = a.family_account_id and main_payer = 1");
				$query->addJoin(" LEFT JOIN ". Payer::_tablename . " p on p.id = fap.payer_id " );
			}
			
			if(isset($param['location'])) {
				Zend_Loader::loadClass("RegistrationChildDayTime");
				$query->addJoin(" a.id in  (select registration_id from . ". RegistrationChild::_tablename ." rc1 LEFT JOIN ". RegistrationChildDayTime::_tablename  ." rcdt on rcdt.registration_child_id = rc1.id where location_id = ". $param['location'] ." ) " );
			}
			
			if(isset($param['category'])) {
				$query->addWhere(" session_category_id = ". $param['category'] );
			}
		} else {
			Zend_Loader::loadClass("FamilyAccountPayer");
			$query->addJoin(" LEFT JOIN ". FamilyAccountPayer::_tablename . " fap on fap.family_account_id = a.family_account_id and main_payer = 1");
			$query->addJoin(" LEFT JOIN ". Payer::_tablename . " p on p.id = fap.payer_id " );
		}
		$query->addJoin(" LEFT JOIN ". PayerContact::_tablename . " pc on pc.payer_id = p.id  and contact_type_id = 11 "  );
		$query->addSelect("pc.value as payer_email");
		
			
		$query->addWhere(" a.deleted = 0 and a.cancelled = 0");
		$query->addHaving(" amount > 0 ");
		//echo $query->createSQL();die();
		$db = DBCon::instance();
		$result = $db->executeQuery($query->createSQL());

		$data = array();
		$count = 0;
		$registration_id = 0;
		$sessionDays = new CatalogDays();
		$sessionSched = new CatalogSchedules();
		$shortDays = TimeUtility::getDaysOfWeekShortNames();
		$regDays= new RegistrationDays();
		foreach($result as $key => $value){
			$count = $count + 1;
			$start_date = $value['year'].'-'.$value['month'].'-01';
			$end_date = TimeUtility::lastday($value['month'],$value['year']);
			$data[$value['id']]['catalog_name'] = $value['name'];
			$data[$value['id']]['firstname'] = $value['firstname'];
			$data[$value['id']]['lastname'] = $value['lastname'];
			$data[$value['id']]['id_number'] = $value['id_number'];
			$data[$value['id']]['start_date'] = $value['start_date'];
			$data[$value['id']]['catalog_id'] = $value['catalog_session_id'];
			$data[$value['id']]['costArray'][$count] = array('year'=>$value['year'],'month'=>$value['mymonth'],'scholarship'=>$value['scholarship'],'discount'=>$value['discount'],'cost'=>$value['amount'],'registration_month_id'=>$value['registration_month_id'],'monthno'=>$value['month'],'start_date'=>$start_date,'end_date'=>$end_date,'registration_id'=>$value['id'],'pricetierId'=>$value['price_tier_id']);
			$data[$value['id']]['allow_monthly'] = $value['allow_monthly'];
			$data[$value['id']]['allow_biweekly'] = $value['allow_biweekly'];
			$data[$value['id']]['allow_weekly'] = $value['allow_weekly'];
			$data[$value['id']]['allow_partial'] = $value['allow_partial'];
			$data[$value['id']]['registration_id'] = $value['id'];
			$data[$value['id']]['catalog_session_id'] = $value['catalog_session_id'];
			$data[$value['id']]['family_account_id'] = $value['family_account_id'];
			$data[$value['id']]['payer_firstname'] = $value['payer_firstname'];
			$data[$value['id']]['payer_lastname'] = $value['payer_lastname'];
			$data[$value['id']]['payerId'] = $value['payerId'];
			$data[$value['id']]['payer_email'] = $value['payer_email'];
			if($value['id'] != $registration_id ) {
				
				$regDays->clear();
				$regDays->loadWhere("registration_id = ". $value['id'] );
				$regDaysArray = $regDays->toArray();
				if(isset($regDaysArray[0]['id']) && $regDaysArray[0]['id'] > 0) {
					foreach($regDaysArray as $day => $dayvalue){
						$data[$value['id']]['days'][] =  $shortDays[$dayvalue['day']];
					}
				} else {
					$sessionDays->clear();
					$sessionDays->loadWhere(" catalog_session_id = ". $value['catalog_session_id']);
					foreach($sessionDays->toArray() as $day => $dayvalue){
						$data[$value['id']]['days'][] =  $shortDays[$dayvalue['day_number']];
					}
				}
				
				$sessionSched->clear();
				$sessionSched->loadWhere(" catalog_session_id = ". $value['catalog_session_id'] );
				$data[$value['id']]['time'] = $sessionSched->toArray();
				
				$data[$value['id']]['monthArray'][0] = array('scholarship'=>0,'discount'=>0,'cost'=>0,'costindex'=>0);
			}
			$registration_id = $value['id'];
			
			
		}
		//print_r($data);
		return $data;
		
	}
	
	public function updatePaymentCart($items,$month,$scholarship,$discount,$payAmount,$origAmount,$dueAmount,$allCost=0){
	
		Zend_Loader::loadClass("CartUtility");
		$payment = CartUtility::getMakePayment();
		//print_r($items[1]['costArray']);
		$newitem = array();
		if(count($month)) {
			foreach($items as $registration_id => $value){
				
				foreach($items[$registration_id]['monthArray'] as $key => $valuex ){
					if( $payAmount[$registration_id][$key] > 0 ) {
						
						$items[$registration_id]['monthArray'][$key]['scholarship'] = $scholarship[$registration_id][$key];
						$items[$registration_id]['monthArray'][$key]['discount'] = $discount[$registration_id][$key];
						$items[$registration_id]['monthArray'][$key]['cost'] = $payAmount[$registration_id][$key];
						$items[$registration_id]['monthArray'][$key]['costindex'] = $month[$registration_id][$key];
						$items[$registration_id]['monthArray'][$key]['origAmount'] = $origAmount[$registration_id][$key];
						$items[$registration_id]['monthArray'][$key]['due'] = $dueAmount[$registration_id][$key];
						$items[$registration_id]['monthArray'][$key]['start_date'] = $value['costArray'][$month[$registration_id][$key]]['start_date'];
						$items[$registration_id]['monthArray'][$key]['end_date'] = $value['costArray'][$month[$registration_id][$key]]['end_date'];
						
					}

				}
				if($allCost == 0) {
					if($items[$registration_id]['monthArray'][0]['cost'] > 0 ) 
						$newitem[$registration_id] = $items[$registration_id];
				} else {
					$newitem[$registration_id] = $items[$registration_id];
				}
			
			} 
		
			$payment->items = $newitem;
		} else {
			$newitem = $payment->items;
		}
		return $newitem;
				
	}
	
	public static function processScheduledPayments()
	{	
		Zend_Loader::loadClass("AccountingPaymentRecurringRows");
		Zend_Loader::loadClass("CodesUtility");
		Zend_Loader::loadClass("AuthUtility");
		Zend_Loader::loadClass("BaseLogger");
		Zend_Loader::loadClass("AccountingPaymentRecurring");
		Zend_Loader::loadClass("Payer");
		
		
		//echo "mag punit na kog scheduled payments.....". "<br>";
		//echo AuthUtility::getCurrentUserId() . "<br>";
		//echo AuthUtility::getCurrentCcKey() . "<br>";
		
		$aprRows = new AccountingPaymentRecurringRows();
		$paymentRecurring = new AccountingPaymentRecurring();
		$today = date("Y-m-d");
		//$today = "2009-10-01";
		$aprArray = $aprRows->getPaymentScheduleArray(0,$today);
		$logger = new BaseLogger();
		
		if($aprArray){
			foreach($aprArray as $apr){
				//echo "<pre>";print_r($apr);echo "</pre>";
				$unInvoicedArray = PaymentUtility::getListchildren($apr['family_account_id'],'','',0,1,true,false,$apr['registration_month_id']);
				$unInvoicedAmount = 0;
				if(isset($unInvoicedArray[0])){
					$unInvoiced = $unInvoicedArray[0];
					$unInvoicedAmount = $unInvoiced['cost'];
				}
				$schedPayAmount = $apr['amount'];
				if($unInvoicedAmount < $schedPayAmount){
					$forInvoiceAmount = $unInvoicedAmount;
				}else{
					$forInvoiceAmount = $schedPayAmount;
				}
				$invoiceId = 0;
				$operation_xid = 0;
				if($forInvoiceAmount > 0){
					//create invoice here....
					$regBreakDown = array();
					$regArr = array('due'=>$forInvoiceAmount,'month'=>$apr['month'],'year'=>$apr['year'],'registration_id'=>$apr['regId']);
					$regBreakDown[] = $regArr;
					
					$payer = new Payer();
					$email = $payer->getPayerEmail($apr['payer_id']);
					$myemail = $email[0]['email'];
					
					
					$result = PaymentUtility::transactCreditCardPayment($forInvoiceAmount , $apr['payer_id'] , $apr['payer_card_id']);
					if($result->code == CodesUtility::TRANSACTION_CODE_SUCCESS){
						$operation_xid = $result->operation_xid;
					}else{
						$logger->logError("Card payment failed :" . $result->msg);
						//email that transaction failed...
						PaymentUtility::emailFailedCardPayment($myemail, $apr['payer_card_id'],$result->msg);
					}
					
					if($operation_xid){
						$invoiceId = PaymentUtility::generateInvoice($regBreakDown,"Automatic Invoicing",$apr['payer_id'],0,FALSE,null,$apr['family_account_id']);
						$regBreakDown = array();
						$regArr['amount'] = $forInvoiceAmount;
						$regArr['accounting_invoice_id'] = $invoiceId;
						$regArr['registration_month_id'] = $apr['registration_month_id'];
						$regBreakDown[$apr['regId']][] = $regArr;
						//echo "<pre>";print_r($regBreakDown);echo "</pre>";
						
						$accountingPaymentId = PaymentUtility::generatePayment(CodesUtility::PAYMENT_TYPE_CREDIT_CARD , $apr['payer_id']  , $forInvoiceAmount , $forInvoiceAmount, 0 , 
							$apr['payer_card_id'] , null, null , null , $regBreakDown , null, 0, $apr['family_account_id'] ,
							'',$operation_xid);
						
						
						
						if($myemail != '') {
							PaymentUtility::emailPayment($accountingPaymentId,'Your Payment',"",$myemail,"");
						}
					}
					//echo "xid: ". $operation_xid . "<br>";
					//echo "<pre>";print_r($unInvoiced);echo "</pre>";
					//echo "<br> invoiceId : ". $invoiceId;
				}
				
				$paymentRecurring->clear();
				$paymentRecurring->loadById($apr['id']);
				if($operation_xid){
					//set processaed flag sa rec_payment_setup	
					$paymentRecurring->processed = 1;
					//save $accountingPaymentId
				}else{
					$paymentRecurring->processed = 2;
				}
				$paymentRecurring->save();
				
			}
		}
		
	}
	
	public static function emailFailedCardPayment($toemail = "", $payer_card_id = 0, $ErrorMsg="" )
	{
		if(!$toemail || !$payer_card_id){
			return;
		}
		
		Zend_loader::loadClass("Zend_Mail_Transport_Smtp");
		Zend_loader::loadClass("Zend_Mail");
		Zend_loader::loadClass("CodesUtility");
		Zend_loader::loadClass("Administrator");
		Zend_loader::loadClass("AuthUtility");
		Zend_Loader::loadClass("Payer");
		Zend_Loader::loadClass("AccountingPayments");
		Zend_Loader::loadClass("GeneralSettings");
		Zend_Loader::loadClass("SetupUtility");
		
		$client_session = new Zend_Session_Namespace(SESSION_CLIENT);
		$config = $client_session->config;
			
		$userid = AuthUtility::getCurrentUserId();
		$admin = new Administrator();
		$admin->loadWhere("id = ". $userid);
		$email = $admin->email;
		if(empty($email)){
			$email = $config->email->sourceaddress;
		}
	
		$settings = new GeneralSettings();
		$settings->load();
		$settingvalues = $settings->toArray();
		
		if (!empty($settingvalues))
		{
			foreach($settingvalues as $item)
			{
				switch($item["code"])
				{
					case SetupUtility::ORGANIZATION_NAME:
						$org_name = $item["value"];
						break;
					case SetupUtility::CONTACT_EMAIL:
						$contact_email = $item["value"];
						break;
					case SetupUtility::CONTACT_PHONE:
						$contact_phone = $item["value"];
						break;
					case SetupUtility::CONTACT_NAME:
						$contact_name = $item["value"];
						break;
				}
			}
		}
		
		$from = $email;
		$transport = new Zend_Mail_Transport_Smtp(CodesUtility::SMTP,array('port' => CodesUtility::SMTP_PORT));
		Zend_Mail::setDefaultTransport($transport);
		
		$mailer = new Zend_Mail();
		
		$payerCard = new PayerCard();
		$payerCard->loadById($payer_card_id);
		
		$payerInfo = new Payer();
		$payerInfo->loadById($payerCard->payer_id);		
		
		$body1 = 'Dear '. $payerInfo->firstname .',<br>';
		$body1 .= 'This is to inform you that we were not able to process your scheduled payment using the credit card below.';
		$body1 .= '<br><br>';
		
		$body2 = 'Name: '. $payerInfo->firstname . ' ' . $payerInfo->lastname . '<br>';
		$body2 .= 'Credit Card Type: ' . $payerCard->CardType->value . '<br>';
		$body2 .= 'Credit Number: '. "xxxx-xxxx-xxxx-" . $payerCard->card_number . '<br>';
		$body2 .= '<br><br>';
		$body2 .= 'Due To: '. $ErrorMsg . '<br>';
		
		$body3 = '<br><br>Sincerely,<br><br><br>';
		$body3 .= $contact_name."<br>";
		$body3 .= $org_name."<br>";
		$body3 .= $contact_phone."<br>";
		$body3 .= $contact_email."<br><br>";
		
		$body3 .= "<a href='".$config->client->host ."'>CLICK HERE</a> to login to your Family Account to update you credit card info and call us if you want to reprocess your payment.";
		
		$subject = CodesUtility::FAILED_PAYMENT_SUBJECT .' from ' .$org_name;
		
		$mailer->setBodyHTML($body1 . $body2 . $body3);
		$mailer->setFrom($from);
		$mailer->addTo($toemail);
		$mailer->setSubject($subject);
		try{
			$mailer->send();
		}catch(Exception $e){
			$logger = new BaseLogger();
			$logger->logError("Fail sending Email.. ".$e->getMessage()." \n ".$e->getTraceAsString()."\n");
		}
		
		if($contact_email){
			$mailer_confirmation = new Zend_Mail();
			$mailer_confirmation->setBodyHTML("Failed Care Payment is sent to :". $toemail ."<br>". $body2);
			$mailer_confirmation->setFrom($email);
			$mailer_confirmation->addTo($contact_email);
			$mailer_confirmation->setSubject("Failed Care Payment Notification ");
			try {
				$mailer_confirmation->send();
			} catch(Exception $e){
				$logger = new BaseLogger();
				$logger->logError("Fail sending Email.. ".$e->getMessage()." \n ".$e->getTraceAsString()."\n");
			}
		}
		
		
	}

	public function generateCreditOnAccount($family_account_id,$amount,$regData=array()){
	
	//print_r($regData);die();
		Zend_Loader::loadClass("AccountingJournal");
		Zend_Loader::loadClass("AccountingJournalItem");
		Zend_Loader::loadClass("CodesUtility");
		Zend_Loader::loadClass("AccountCodes");
		Zend_Loader::loadClass("AccountingJournalRegistration");
		Zend_Loader::loadClass("AccountingRefund");
		Zend_Loader::loadClass("AuthUtility");
		
		$user_id = AuthUtility::getCurrentUserId();
		$accountCodes = new AccountCodes();
		$accountCodesArray = $accountCodes->getAccountCodesArray("short_name", "account_code");
		
		$journal = new AccountingJournal();
		$journal_id = $journal->newJournal($family_account_id,CodesUtility::TRANS_TYPE_CREDIT_ON_ACCOUNT,TimeUtility::convertToSQLDateFormat(date('m/d/Y H:i:s'), true),1);
		
		$refund = new AccountingRefund();
		$refund->newRefund($journal_id,$regData['payer_id'],$user_id,$regData['payment_id'],"");
		
		$journal_item = new AccountingJournalItem();
		$journal_item->newJournalItem($journal_id,$accountCodesArray[CodesUtility::ACCOUNT_CODE_COA],$amount,CodesUtility::ACCOUNTING_TYPE_CREDIT,2);
		$journal_item->clear();
		$journal_item->newJournalItem($journal_id,$accountCodesArray[CodesUtility::ACCOUNT_CODE_SALES],$amount,CodesUtility::ACCOUNTING_TYPE_DEBIT,2);
		
		$journalItemRegistration = new AccountingJournalRegistration();
		$journalItemRegistration->newJournalRegistration($journal_id,$regData['registration_id'],$regData['registration_month_id'],$regData['accounting_invoice_id'],$amount,0);
		
		
		
	}
	
	public function addToInvoice($registrations,$invoiceid){
		
		Zend_loader::loadClass("AccountingInvoice");
		Zend_loader::loadClass("AccountingJournalRegistration");
		Zend_Loader::loadClass("RegistrationMonth");
		
		
		$invoice = new AccountingInvoice();
		$invoice->loadWhere(" id = ". $invoiceid);
		$journal_id = $invoice->journal_id;
		$regMonth = new RegistrationMonth();
		$journalDetails = new AccountingJournalRegistration();
		foreach($registrations as $key => $value){
			$regMonthId = 0;
			$regMonth->clear();
			$regMonth->loadWhere(" registration_id =  ". $value['registration_id'] . " and month = ". $value['month'] . " and year = ". $value['year'] );	
			$regMonthId = $regMonth->id;
			
			
			
			$journalDetails->clear();
			$journalDetails->accounting_journal_id = $journal_id;
			$journalDetails->registration_id = $value['registration_id'];
			$journalDetails->registration_month_id = $regMonthId;
			$journalDetails->accounting_invoice_id = $invoiceid;
			$journalDetails->amount = $value['due'];
			$journalDetails->save();
			
			if($value['due'] > 0 ) {
				
				Zend_Loader::loadClass("AccountCodes");
				Zend_Loader::loadClass("CodesUtility");
				Zend_Loader::loadClass("AccountingJournalItem");
				
				$journalitems = new AccountingJournalItem();
				$accountCodes = new AccountCodes();
				$accountCodesArray = $accountCodes->getAccountCodesArray("short_name", "account_code");
				$journalitems->clear();
				$journalitems->journal_id = $journal_id;
				$journalitems->account_code = $accountCodesArray[CodesUtility::ACCOUNT_CODE_AR];
				$journalitems->cost = $value['due'];
				$journalitems->accounting_type = CodesUtility::ACCOUNTING_TYPE_DEBIT;
				$journalitems->account_type = CodesUtility::ACCOUNT_TYPE_REGISTRATIONS;
				$journalitems->save();
					
				$journalitems->clear();
				$journalitems->journal_id = $journal_id;
				$journalitems->account_code = $accountCodesArray[CodesUtility::ACCOUNT_CODE_SALES];
				$journalitems->cost = $value['due'];
				$journalitems->accounting_type = CodesUtility::ACCOUNTING_TYPE_CREDIT;
				$journalitems->account_type = CodesUtility::ACCOUNT_TYPE_REGISTRATIONS;
				$journalitems->save();
			}
		}
	
		
	}
	
	
	
	
 }